7.1 Why does my spreadsheet say 0.1+0.1+0.1+0.1+0.1-0.5 isn't 0?
Description
This article is from the Spreadsheets FAQ, by Russell Schulz casfaq@locutus.ofB.ORG with numerous contributions by others.
7.1 Why does my spreadsheet say 0.1+0.1+0.1+0.1+0.1-0.5 isn't 0?
if any computer package uses binary floating point numbers (and
the vast majority do), then it can not represent the value 1/10
exactly (just like it can't represent the value 1/3 exactly).
so, in each 0.1 there's a little roundoff error, and when you get
enough of those errors together, you can sometimes see them.
this is why you cannot reliably use simple equality tests on a
binary-arithmetic computer (including in a spreadsheet) unless it
is designed to represent such quantities exactly (for instance,
by using integer hundredths, or using Binary Coded Decimal form).
so, instead of testing for equality like
=IF(Credits=Debits)
you have to do it something like
=IF(ABS(Credits-Debits)<0.0000001)
because the first method MAY FAIL when you encounter this type
of roundoff error. the numbers will _look_ the same on the
screen, but they won't _be_ the same, when examined to the full
precision the computer has used.
frustratingly, Microsoft Excel _does_ store values as integer
hundredths to make the files smaller, but it does _not_ use these
precise values in its calculations, as that perfect accuracy
would make it slower!
if you're using dollars and cents, testing if the values agree
to within 1 cent (0.01) usually makes more sense than 0.0000001 .
even though later versions of Microsoft Excel do have an option
`use values as displayed', it does not wholly work around this
problem, as the calculations are still done internally in binary.
however, the final test against `0' _could_ work, if placed in
separate cells: the displayed value for 0.1+0.1+0.1+0.1+0.1-0.5
(when forced to show at most 2 decimal places) is exactly 0.
but if you were testing `0.1+0.1-0.1' against `0.1', all bets
are off -- it may work, or it may not, depending on how careful
the different parts of the spreadsheet are in being consistent
with rounding to impossible-to-store-exactly values like `0.1'.
Continue to:
My Books
