This BBC news article reminded me that I wanted to write a short piece about spreadsheets, and in particular about an entirely non-obvious danger that spreadsheets pose to their users.
What do I mean? Well, in particular, calculators and spreadsheets may give different answers for the same calculations! That fact is surprising to many, even to people who should know that that is the case.
Why does this happen, and why should I care? OK, so the first thing you need to know is that the calculator on your desk probably represents numbers the way you think about them — i.e. in decimal. So, on your calculator, when you see 2.1 displayed on the screen, the number the calculator holds in its memory really is 2.1.
Your computer, on the other hand, prefers to use binary rather than decimal to store numbers, the reason being that manipulating binary numbers is hugely faster for a computer. Now, in binary, 2.1 is 10.0001100110011… a recurring fraction. As a result, when your spreadsheet shows you the number 2.1, it is lying. The number it has in its memory is not 2.1; it is very close to 2.1, but it is actually 2.0999999…
I don’t care, you say. Well, maybe you do, maybe you don’t. For instance, if you take your calculator and enter 0.1+0.1+0.1-0.3, you get the expected answer 0. If you do the same in a spreadsheet, it may show you 0, but it will actually have calculated something like 5.55×10-17. Similarly, on your calculator, 0.1×0.1-0.01 is 0, whereas on your computer, it is very probably around 1.73×10-18.
Worse, the chances are that the people who wrote your spreadsheet software knew that this problem existed, and so they try to hide it from you. Well-written binary floating point libraries will always attempt to find the shortest decimal that matches the binary representation they have, so you will often find that the answer looks the same on the screen.
At this point, unless you’re a pedant, you probably still believe that you don’t care — after all, the inaccuracy is very small. But let me convince you otherwise; imagine you are an examiner, marking an exam script. Further imagine that students have been told to present their answers correctly rounded to two decimal places. Set the cells in your spreadsheet to round to two decimal places (this is usually an option under the Format menu) and enter the following into a cell:
=3.013 * 5
You should see the correctly rounded answer, 15.07 (the actual answer is 15.065). Now let’s imagine we are also told to subtract 15 from it; enter
=3.013 * 5 - 15
The chances are quite good that your spreadsheet is now showing 0.06, and not 0.07. Entering the same thing on your calculator should verify that this is wrong(you’ll get 15.065, minus 15 = 0.065, rounded to 2 d.p. is 0.07).
If the exam board had provided a spreadsheet to its examiners to help with the marking, and it causes this kind of error, students are going to lose marks for writing the correct answer. That might make the difference between someone going to university and not; you might have messed up their entire life simply because you didn’t understand that spreadsheets do arithmetic in binary and not decimal.
How can we fix this problem? Well, computers can accurately represent integers, so you could just multiply everything by 1,000 and then divide at the end; i.e. enter
=(3013 * 5 - 15000) / 1000
which will correctly round to 0.07. Yes, that’s right, you get different answers in your spreadsheet from
=(3.013 * 5 - 15)
and
=(3013 * 5 - 15000) / 1000
and not only that, but the latter is more accurate in spite of having an extra calculation in it (welcome to floating point, by the way).
The best solution, of course, is to use something that does decimal arithmetic when you actually care about having an accurate decimal result.