** Update 2023 Added When Excel goes ( is used badly ) impacting careers
I have an Excel spreadsheet that I use for keeping the accounts for a small club. The spreadsheet has one page for income, one page for expenditure and another page that totals up and displays current balances. On the income and expenditure page I add an entry for each transaction. The line starts with a total amount for that transaction and then the same amount spread across various columns depending on which items that transaction relates to. I then sum the columns and they should cross match. The sheet uses simple conditional colouring red and green to indicate when numbers that should add up don't add up.
I was just about to send a spreadsheet to a new colleague when I noticed one of the red indicators showing and yet despite the numbers appearing to add up correctly. After extracting the relevant numbers into a fresh new spreadsheet, I could see that the difference between the two sums was very very small, a rounding error of 0.0000000000109139
Excel has a feature where you can set the display precision of numbers typically used for currency, that its two decimal places. However, internally it will work to much higher position unless a certain option is checked for the spreadsheet. That option "set precision as displayed" is supposed to ensure that Excel uses the same precision for calculations and display. This is generally a dangerous option as you can get accumulations of errors through lack of precision in complex sheets.
On the sheet extract shown below the indicator square has the formula
=IF(SUM(B8:B397)=SUM(E4:T4),"MATCH","FAIL")
The vertical numbers are summed into square C6, and the horizontal numbers summed into D6. Subtracting C6 from D6, gives us the size of the rounding error in square D8.
Changing the Excel option for "set precision as display" changes (and clicking on the warning) the value of the displayed rounding error shown in D8 to 0 as one would expect. However, the indicator square remains at FAIL showing that the interior precision is maintained during the above type of test operations.
The fix was to change the indicator to use ABS(C6-D6)> 0.000001 as the test for a match.
 |
| On a Mac the option can be found at Excel -> Preferences -> Calculations |
Using Excel for Mac 16.67.