This note describes a really irritating problem where a formula is “correct” but sometimes returns the wrong answer because of precision problems with storing fractions accurately in spreadsheets cells. Read on to see two examples of the problem and successful workarounds.
The examples shown here are from the Buggy “NZ Rounding” spreadsheet (XLS 34 KB). See also the other articles on rounding.
NZ Coinage Example
Here in New Zealand we have recently changed our coinage. We no longer have 5c coins. This follows the dropping of 1c and 2c coins some years ago. When paying for goods with dollar notes and coins, rather than electronically or by cheque, etc, the total price has to be rounded to the nearest 10 cents. The most common method being adopted commercially is to:
- round down if the total ends in 1 to 5 cents
- round up if it ends in 6 to 9 cents.How easy is it to do this “NZ rounding” in a spreadsheet given that it is not quite the same as conventional rounding? In conventional rounding totals ending in 5 would round up rather than down. Both are shown here. The only case where the results are different is when the unrounded value ends in 5 as in green for row 3. The conventional formula, used in cell C2, for rounding A2 to the nearest 10 cents is =ROUND(A2,1).
There are a number of different spreadsheet formulas that can be used to do NZ rounding. The first, simple minded, formula we came up with has correct logic but as shown on the next page it doesn't always work. This formula for B2 is:
The correct answers are in column C. Sometimes, as the yellow highlighted values show, when the cents part ends in 6, the answers, from our formula, are wrong. We experimented the one shown emample with various other sequences and the formula isn’t giving incorrect answers consistently or predictably!
Changing the <0.06 in the formula to <= 0.5 doesn’t help the situation either. The same results occur.
This problem occurs because decimal fractions such as .16 and .36 can’t be represented exactly as binary fractions. Only fractions related directly to powers of 2 such as ½ = 0.5, ¼ = 0.25, 3/8 = 0.375, etc are stored exactly. (Read more about this in http://support.microsoft.com/kb/78113).
Consequently there can be difficulties with comparisons of the form that we are using here. When we ask if A6 – ROUNDDOWN(A6,1) < 0.6, this should evaluate to false because 0.6 is obviously not < 0.6, we sometimes get the wrong result. This happens because, in Excel, A6 – ROUNDDOWN(A6,1) actually evaluates to 0.0599999999999987 rather than 0.6. (There are several different techniques for evaluating formulas in Excel and these are written about in the article on Evaluating Formulas.)
How can we fix the problem?
In this particular case, where we are dealing with currency, we need only to work in cents so we can round the expression to 2 decimal places before doing the comparison. This results in the rather clumsy formula:
=IF(ROUND(A2-ROUNDDOWN(A2,1),2)<0.06, ROUNDDOWN(A2,1), ROUNDUP(A2,1))
When this formula is copied down the column the “incorrect” results disappear.
An even better solution
In this particular case there is an even simpler solution which we eventually came up with. “NZ rounding” is easily achieved by subtracting one cent (0.01) from the total before rounding it to 1 decimal place. Thus the correct formula in column C in our example is generated by copying the formula in C2, =ROUND(A2-0.01,1) down the rest of the column.
The same type of problem arose, for exactly the same reason, in another spreadsheet with a special rounding rule. In this case we needed a formula (believe it or not it was for calculating the number of bottles of sauce required to make a large number of pizzas!) which rounded up using 0.3 rather than 0.5. The formula used was
=IF(B4-INT(B4)<=0.3,INT(B4),INT(B4)+1) (the INT function is equivalent to ROUNDDOWN( ,0) for positive numbers.)
Once again the formula worked in most situations but sometimes when the difference B4-INT(B4) was supposed to be exactly 0.3 the formula failed. In this case the same cure could have been used but we used an alternative. The formula was changed to
=IF(B4-INT(B4)<=0.300000001,INT(B4),INT(B4)+1) and this got around the problem.
Do other spreadsheet packages have precision errors or is it just Excel?
Over the years we have encountered similar problems with Quattro Pro and Lotus 123. Until recently we thought OpenOffice.org’s Calc didn’t have such problems. However, the “NZ rounding” solution, using the IF, has similar problems when opened in Calc.
Precision errors of this sort are tricky because they can’t easily be detected. They happen seemingly inconsistently and unpredictably. The moral of the story seems to be look out for calculations that involve comparisons of fractions that will not be exactly represented by binary!