# Monday Morning Mulling: June Challenge

3 July 2017

*On the final Friday of each month, set an Excel for you to puzzle over for the weekend. On the Monday, we publish a solution. If you think there is an alternative answer, feel free to email us. We’ll feel free to ignore you.*

*Final Friday Fix: June Challenge Recap *

Which came first? The spreadsheet or the **SUM **function? Who knows and more to the point, who cares? Well, you might care if **SUM** does not appear to work in a spreadsheet:

The attached Excel file demonstrates this quirk. The question is, how did it happen?

*The Solution*

Since **SUM** has been used in models for over 30 years, you are looking in the wrong place if you think the function is to blame. **SUM** sums numbers, plain and simple. Given there are no hidden rows and columns here, the first thing to check is are the values in cells **C3:C7** actually numbers, *i.e.* do they have a numerical value?

Consider the following example where the number ‘3’ below has been entered as text:

Cell **B4 **in the above illustration has had an apostrophe added before the value 3. This makes Excel treat this value as text, not a number. Text is disregarded in a summation by Excel, therefore the formula

**=SUM(B2:B6)**

only sums 1, 2, 4 and 5 – the total of which is 12, not 15.

Extrapolating this idea, this means that the **SUM** function would produce a total less than the apparent total not more. Furthermore, the functions **VALUE()** and **N()**, both of which consider the value of a cell’s contents suggest nothing is amiss:

Hang on a minute. Check out that formula in cell **K8**; it clearly states that cell **C8** equals the sum of the above. Yes, I know that is where we started – but this gives us slightly more information than we had originally in a subtle sort of way.

We have determined that cells **C3:C8 **are all values and that **C8 **is the sum of the above. Therefore, it is the appearance of cell **C8** that is now in question, not its logic or value. If you click on cell **C8 **and then use **CTRL + F1** to format cells you discover the following:

Aha! The cell has been formatted as

**“21”;”21”**

Custom number formatting has been used to trick you. If the number is either non-negative or negative (*i.e.* it is a number!), the value 21 is displayed *regardless of the true numerical value*.

Did you spot it? Well done if you did, but don’t feel too badly if you didn’t. This example comes from a fraudulent model the author uncovered years ago which conned auditors, investors and a well-known bank out of millions of dollars. The problem was the model had been audited by auditors but not **model** auditors – whom possess a different skill entirely.

You can find out more about custom number formatting here. If you want to know more about auditing. or believe your model may require a review of some sort, please drop us a line at contact@sumproduct.com.

For more tricks and tips, check out our many examples at www.sumproduct.com/thought.

* *

*The Final Friday Fix will return on Friday 28 July with a new Excel Challenge. In the meantime, please look out for the Daily Excel Tip on our home page and watch out for a new blog every other business workday.*