Modelling tips and tricks: SUM things to consider
SUM is a function everyone thinks they know well – but do they? Excel MVP Liam Bastick explores the pros and pitfalls of this popular formula.
SUM may include cells, numbers or ranges. In the context of financial modelling, summations are usually of numbers either directly above or to the left of the cell in question:
There is a great keyboard shortcut available on most computers. If you select the cell to the right or directly below the values to be aggregated and then use the shortcut ALT + = you will see that the range is summed automatically.
I use this all of the time in modelling. It’s a fast shortcut, it ensures you don’t miss cells within the range, it requires the range to be contiguous and you can’t leave blank cells. This shortcut actually forces you to build in a manner that will reduce the number of errors you might make. This reinforces one of my on-going crusades:
A lazy modeller is to be encouraged (it encourages efficiency); lazy modelling isn’t.
Using ALT + = only works if there aren’t blank cells between values. Believe it or not, I think that’s a good thing. Let me explain. Imagine I had created an output which contained blank rows, such as the following Indirect Cash Flow Statement:
Do you see only the highlighted cells in column J have been included in the summation formula in cell J56?
ie cells J45, J49, J52 and J55 have all been specifically excluded. This is how it should be. If instead I had used the summation formula
Industry insightsView more
these cells would have been included. Don’t see the problem? The issue is often end users will add percentages or other analysis into blank cells on output sheets. No matter how small these numbers, they will lead to Balance Sheet or other reconciliation errors. If they are not numbers, they can lead to #VALUE! errors or sums being incorrect (see below) instead. In short, try not to aggregate blank cells.
That’s not the only issue with SUM though. Consider the following example:
In this example, I have totalled the values in cells E3:E7 in two distinct ways: the first uses the aforementioned SUM function with ALT + =, the other has added each cell individually using the ‘+’ operator. Are you thinking you’d be mad to use the alternative (second) approach – especially if there were many more rows?
Well, take another look:
In this example, cell E5 has been modified. It has been stored as text, even though it looks like the number 3. SUM treats this as having zero value whereas the more convoluted addition carries on regardless. Simplest may not always be bestest.
In an example like the one above, this may be easy to spot, but would you stake your life that this sum is correct?
There is a simple way to check using the COUNT function. COUNT counts the number of numbers in a range, so we can use it to spot numbers that aren’t numbers:
Here, the formula in column I highlights when a number is not a number. Note how it reports by exception: if the cell in question contains a number then COUNT(Cell_Reference) equals 1 and =1-COUNT(Cell_Reference) equals zero. Only non-numbers will be highlighted – it’s better to know I have two errors rather than 14,367 values working correctly.
If you don’t think this applies to you, have you ever worked with PivotTables? This article isn’t about PivotTables, but as an aside, for those of you who have ever worked with this Excel feature, have you ever been frustrated when the following has happened?
You want your aggregation of values to default to SUM but instead they display as COUNT. This could be highlighting that some of your data is non-numerical and/or blank. In other words, the calculations may be correct but the results may be wrong. Just a thought.
Liam Bastick is author of an Introduction to Financial Modelling, which provides a simple walkthrough of the common perils and pitfalls of financial modelling. Presently, this book is only available on Amazon Australia but if anyone would like to buy a copy directly contact Liam via this link. AccountingWEB readers receive a 10% discount plus p&p (including tracking and insurance at cost).
For readers in Australia or those who just fancy a trip Down Under, Liam will be speaking at Excel Summit South 2019, a two-day Excel event in Brisbane, Melbourne, Perth and Sydney in late July to early August with speakers from Microsoft and Excel experts. For more dates and details visit the website. Especially for AccountingWEB readers there is a 10% discount on all prices – simply quote the code ACCOUNTINGWEB10 when purchasing.
Recognised by Microsoft as one of 104 Most Valuable Professionals (MVPs) in Excel worldwide by Microsoft, Liam has over 30 years’ experience in financial model development/auditing, valuations, M&A, strategy, training and consultancy. He has headed Ernst & Young’s modelling team in Melbourne and was an Assistant Director in their...