Spreadsheets, and technology in general for that matter, can be fantastic. Until that is, things don’t go as expected. It’s all too easy for an entire month’s productivity gains to be lost when sorting out a single spreadsheet problem. In this series, we will be look at some examples of Excel not doing what we expect and at how to deal with the problems.

In part 1, we will be starting with a pretty basic problem – our numbers don’t add up.

Some of the reasons for numbers appearing not to add up are obvious. Rounding errors can lead to the total of a column of numbers not being quite equal to the numbers that appear in that column. We only have to divide a profit of £83 equally between two partners to see the problem. If we format our numbers just to display round pounds, then each partner will apparently receive £42, but using the SUM() function to add up our 2 cells, each containing £42, will show £83. This occurs because the way in which Excel formats a number has no effect on the value itself. Well, it has no effect on the value, unless we have been reckless enough to turn on one of Excel’s most destructive options. More on that in the next episode.

Although displaying our numbers to round pounds displays our values of 41.5 as 42, Excel continues to use the underlying value of 41.5 in any calculations that use the contents of that cell, irrespective of the cosmetic number format that we have applied to it. Hence, we are actually adding up our 2 values of 41.5 to give us our total of 83, rather than adding up the values of 42 that the cells display:

Excel has several different functions that can help us avoid rounding errors. The ROUND() function simply takes a value, or preferably a reference to a cell containing a value, and rounds it to the number of decimal places entered as the second argument of the function:

=ROUND(B4,0)

Positive numbers entered as the second argument increase the number of decimal places to which the number is rounded. Negative numbers round to the left of the decimal place, so entering the second argument as -3 will round to thousands for example.

If cell B4 contains the value 41.5, or a calculation that returns a value of 41.5, then our formula will round the value to 42. We could use ROUND() to round both our 41.5 values to 42 so that our total would be based on 42 rather than 41.5, and our two values displayed as 42 would add up to 84, rather than 83:

Of course, we haven’t quite resolved our problem. Our profit shares now do look as though they add up correctly, but our profit of £83 has become £84. One general solution to this problem would be to calculate our final profit share not as the total profit divided by the number of partners, but as the profit less the sum of the rounded profit shares of each of the other partners, however many partners there might be:

Our formula in cell G5 looks a bit strange. We are using the SUM() function to add up a range of cells that only includes the single cell G4. However, where we have more than two partners, we extend the range in the SUM() function to include all but the last partner:

Another reason why totals might differ from the sum of the individual cells is a previous attempt to deal with a rounding issue involving the addition of a fixed number to a cell or a total. With our example of two values of 42 adding up to 83, just adding +1 at the end of our total formula would change our total to 84 (which, of course, still doesn’t match our profit amount). However, when the values in the individual cells change, then, rather than appearing to solve the problem, our plus one causes another error:

Adherence to principle 14 of the Twenty Principles for Good Spreadsheet Practice should help prevent this possibility:

• 14. Avoid using fixed values within formulas

In the next part of this series, we are going to fulfil our promise to examine one of Excel’s most destructive options. Prepare yourselves for Precision as Destroyed.

You can explore all aspects of Excel, including rounding and the different rounding functions, in the ICAEW archive:

• Excel archive