Category: Blog EN

This quote is from a paper by British statistician George E. P. Box published in 1976 called “Science and Statistics”. Although primarily made in reference to the fields of statistics and analytical models, it can also be applied to financial and other models created in spreadsheets.

A model is a simplified representation of reality. The act of simplification means that all models are wrong because they do not reflect all elements of reality. To have complete accuracy would require e.g., a map on the scale of 1:1. This may well be accurate, but it would not be useful as you could not do any sensible route planning with it – it would be simply far too big and unwieldy.

On the other hand, if we accept a loss of some accuracy, we can have a model which is “wrong” but useful. In physics, for example, the Bohr model of the atom is “wrong” but can nevertheless be very useful in predicting and explaining events in the real world.

The same principle can be applied to spreadsheet models. The key is finding the “sweet spot of usefulness” between accuracy and complexity on the one hand and simplicity and ease of use on the other.

This is not always easy but, as with most skills, you can get better with experience.

Here is my advice to help you:

1.	Think about the level of detail you need in your model before you start work on it.	If appropriate, discuss with colleagues, model sponsors or users before starting. Always bear in mind: (i) the model purpose – what content and functionality is relevant and useful? (ii) the users – what functionality will they understand and be able to use reliably and efficiently (possibly after training)?
2.	Focus on the essentials and use the 80:20 rule.	Often, 80% of the results can be explained by the 20% most significant factors, so focus on these. Minor costs (for example) should not be planned individually but in aggregate as part of an “Other costs” position. If appropriate, focus on the top 10% of factors first to get a working model up and running which can be used for early decision-making and expanded later.
3.	Before adding more complexity, think about how significant the effects could be on results or usability.	If the increase in complexity would bring only a minimal increase in accuracy or functionality, then maybe you should not make the change. Or perhaps a simplified version would suffice. For example, planning sales at individual product level may be too detailed. Planning at brand or business area level may be more suitable.

Once you have made your decisions, be ready and able to explain them.

 

I will conclude with another quote from George Box which summarises the situation very well.

“All models are approximations. Assumptions, whether implied or clearly stated, are never exactly true. … So, the question you need to ask is not "Is the model true?" (it never is) but "Is the model good enough for this particular application?"

Use both SUMs and SUBTOTALs to help understand your data!

• Place SUMs for key data columns such as sales values above your data where you can easily see them and not below your data, which forces you to scroll down – that is inefficient, especially if you have long lists.
  
  
• Also calculate SUBTOTALs. SUBTOTAL is a very useful and flexible function which shows you the sum of your filtered data, very handy when checking or analysing your data.
  
  

The SUBTOTAL function can give various results depending upon the number you enter in brackets before the data range. Most commonly you want the sum, which requires the number 9. Other possibilities are average, count, maximum and minimum. As soon as you type SUBTOTAL( you get a drop-down list of all the options.

I recommend that you calculate both SUM and SUBTOTALS for key data columns in two separate rows, as shown in the screenshot. The SUM result showing the total of all amounts in the column is then always visible and can be linked onwards in the workbook (if necessary) and the SUBTOTAL can be used for filtering and data analysis. If no filters are set, the two formulas should give the same result.

Note: In the example in the screenshot I have used a table but both SUM and SUBTOTAL functions also work with data which is not in a table. In this case, simply enter the cell references as your data range.

Did you know, you can add spaces to formulas to make them easier to read?

Well, you can, and you should! 😊

Inmedievaldaysbookswerewrittenbymonkswithoutspacesbetweenwords.

Did you find that easy to read? Of course, not – it has no spaces. But this was standard practice in medieval books. Over time, however, spaces were added between words to make texts easier to read.

When writing spreadsheet formulas, however, we are still mostly following the medieval monks. Surprise, surprise, this means that formulas – especially those which are longer or more complex – are harder to read. And harder to write, check, amend and understand.

Therefore, I recommend you add spaces to your formulas e.g., after every argument separator (a comma or semi-colon, depending on your regional settings) or before every plus-sign. This makes them easier to read etc. and has no impact on results as Excel ignores them.

Bonus tip: If you want to “retrofit” spaces into your formulas, make a back-up of your file then simply search and replace all argument separators (a comma or semi-colon) with a separator followed by a space.

We’ve all been there… a formula either isn’t accepted by Excel (syntax error), or it gives incorrect results (logic errors). Let’s look at these two cases in turn and see how you can best avoid or fix them.

1. Syntax errors

You finish typing in a formula, press enter, and Excel won’t accept it.

You get a hint such as “You’ve entered too few arguments for this function”. This may sound like Excel being fussy, but it really is quite helpful because you know what the error is, and this helps you find and fix it.

Here is a simple example using the function ROUND.

This simply means you were too quick to press the enter key. 😊

Watch the friendly tool tip which appears under the formula bar when you enter a valid function name followed by an open bracket.

Here Excel shows you exactly which argument you are currently entering by marking the name of the argument in bold. In this case, we have entered a cell reference referring to the cell containing the value that we wish to round and can see that we need a second argument called num_digits. If you are not sure what this means then you have two options.

• FX: Click on the “fx” icon in the formula bar and you open up the helpful “Function Arguments” dialog box which looks like this.

Here you can see the Num_digits argument and on the right-hand side it tells you it needs to be a number. Underneath you get a more detailed explanation. If this still is not enough then click on the link ”Help on this function” at the bottom to open up more details in a browser. These Microsoft help files contain explanations, helpful examples and often a video to help you understand.

• F1: Alternatively simply press F1 to call up the (shorter) help file within Excel.

If you know the function name and the arguments required you can type them in without paying attention to the helpful tooltip. But if you mistype a function name, you will get a #NAME error such as here where I have typed rund instead of round.

Simply retype your function name more slowly and Excel shows you a list of those functions containing the letters you have already typed for you to select the correct one with the correct spelling. It also gives you a helpful short explanation of what the first function does in a tooltip box.

To see what any of the other functions in the list do, simply move down the list with the down arrow on your keyboard or select one with your mouse.

Finally a word on optional arguments. If Excel shows you an argument in square brackets this means it is optional i.e. you don't need to enter anything and can simply close the brackets after you have entered the last compulsory argument (one not in square brackets). Here is an example with the MATCH function where the final argument match_type is optional.

This simply means it is technically optional, but an entry may be very necessary to ensure your function works as desired. In fact with the functions MATCH and VLOOKUP it is usually essential for correct functioning to enter a match type FALSE or 0 to ensure an exact match, otherwise Excel will simply look for the closest matching item, which may give you an incorrect result.

This brings us nicely to the second type of error which is harder to fix.

2. Logic errors

Here you have identified that although Excel has accepted your formula, it gives you an incorrect result.

The first step to solving this type of error is to use the F2 key to analyse the components of your formula.

In this example we can quickly identify that the formula refers to an incorrect cell.

In more complicated examples e.g. with nested IFs or AND conditions, it can be very helpful to click on a selected argument in the tooltip and Excel then marks the relevant section of your formula.

This can be helpful for identifying missing or misplaced brackets or separators (commas or, in this screenshot, semi-colons).

If that still doesn’t solve an issue, you can enlist the help of the Evaluate Formula tool on the Formulas ribbon.

This opens the evaluate formula dialogue box which shows you your formula and by clicking on the evaluate button at the bottom (or by pressing the enter key) Excel shows you how it evaluates the formula step by step so you can see exactly where you may be having a problem and correct the formula.

I hope these tips help to you to avoiding formula errors or, failing that, to detect and correct them.

For lots more tips on how to avoid detect and correct errors please refer to my book “Avoid Excel horror stories” available on Amazon.

STOCKHISTORY function

If you have Office 365 then you can use the function STOCKHISTORY. As the name implies, its main purpose is to enable you to retrieve stock prices, but it can also be used to retrieve historical exchange rates. This can be very useful e.g., for converting historical transactions to the reporting currency or assessing the volatility of foreign currency cash flows.

Exchange rates

In the screenshot you can see an example formula in cell B6. The arguments for the function (the bits inside the brackets) must include two ISO currency codes separated by a colon and a date range (dates from and to). Optionally you can specify the interval (0 = daily, 1 = weekly or 2 = annual) and whether you want opening or closing rates etc.

You only need to enter the formula once! It returns a dynamic array with column headers and as many rows of data as necessary. What is also great is that the values are so-called Formatted Number Values (“FNVs”) which means that Excel automatically formats the values with the correct currency symbol.

Stock prices and data type

You can, of course, also use this great function to get stock price values as the above screenshot showing historical Tesla stock prices demonstrates. The formula is basically the same but here the first input for the function is the stock ticker symbol, here TSLA for Tesla.

You can also define the cell with the ticker symbol as data type stock.

Now when you select the cell, you get an extra box with the option to add extra information such as price, 52 week high and 52 week low.

Data source

The source of all retrieved data is refinitiv, "an LSEG (London Stock Exchange Group) business, [and] one of the world’s largest providers of financial markets data and infrastructure."

More information

You can find out more about this function on the official Microsoft blog.