Modelling tips and tricks: SUM things to consider

plus sign
istock_microstockhub_pl
Share this content
Tags

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:

sum illustration

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:

sum with blanks

Do you see only the highlighted cells in column J have been included in the summation formula in cell J56?

=SUM(J44,J46:J48,J50:J51,J53:J54)

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

=SUM(J44:J55)

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:

sum with text

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:

sum with text revisited

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?

Large sum range

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:

Count illustration

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?

Count in a pivottable

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.

About Liam Bastick

Liam Bastick

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 strategic valuations team in London. He was also a senior member of the UK Post Office’s M&A and strategy teams and has worked for / assisted various other Australian modelling companies including BPM, Corality, Navigator Project Finance, PKF and SumProduct.

He has worked in the UK, Australia, Belgium, Denmark, France, Germany, Hong Kong, Indonesia, Malaysia, New Zealand, United States, Switzerland and Vietnam, with many internationally recognised clients, constructing and reviewing strategic, operational and valuation models for many high profile IPOs, LBOs and strategic assignments.  Liam is a Fellow of the Institute of Chartered Accountants (ICAEW), a Fellow of the Institute of Chartered Management Accountants (CIMA) and is a professional mathematician.

Replies

Please login or register to join the discussion.

avatar
By Alf
24th Jun 2019 08:48

In your cash flow example, i would normally use sum(j44:j55) because if a new row is added, the sum formula will expand correctly and the total will update accordingly. If, however, individual cells are added, or if a cell range is used without a blank end row included, the sum formula will not change when a new row is added but the user may not realise this and assume that the total still works

Thanks (0)