Significant digits

[Opus the Penguin]

This may be in a FAQ somewhere, but I'm not even sure how to search for
it. It's not really about significant digits, but the subject line's
got to say something. If anyone's interested, see if you can reproduce
the following results in Excel. I'm using Excel 2003 SP3.

1. In cell A1, type:
=1.1-0.9
I should get a 0.2, and I do.

2. In cell A2, type:
0.2
(It doesn't really matter if you type the leading zero; it'll get
added. The two cells should look identical, right?)

3. In cell B1, type:
=IF(A1=A2,"Equal","No")
This should come back saying "Equal" and it does.

4. In cell B2, type:
=IF(A1-A2=0,"Equal","No")
Since A1 and A2 are equal, subtracting them should get me zero. Yet
this comes back with a "No" in my Excel 2003 SP3.

Weird, huh? What gives?

I tried formatting the cells to 30 decimal places to see if there was
anything lurking in the insignificant digits. Nothing.

But, wait! Try this step:

5. In cell B3, type
=A1-0.3
That gives me -0.10, just as I'd expect. But format THAT cell to 30
decimal places and ... I get -0.099999999999999900000000000000. (You
can also see this by copying the cell and pasting the value only into
another cell. Then it will still say -0.10, but the formula bar will
tell you a different story.

As I say, what gives? This happens on all three of my computers that
have Excel 2003 SP3. I don't have any other versions to test. None of
the computers is brand new, but none are old Pentium 4s or anything.
The one I discovered the error on is less than two years old--a Dell
Optiplex GX745.

 

[Bernd P]

Hello,

See point 8 of my Excel Don'ts, please:
http://sulprobil.com/html/excel_don_ts.html

Regards,
Bernd

 

[Opus the Penguin]

Hello,

See point 8 of my Excel Don'ts, please:
http://sulprobil.com/html/excel_don_ts.html

Regards,
Bernd

Interesting. The Don't says "Never ever test double precision numbers
with = for equality. Use a test like ABS(a - b) < 1E-13 instead." But
in the example I posted, the = will return a true statement that the
numbers are equal. It's only when I subtract the one from the other
that Excel erroneously says they are not equal.

Since the numbers ARE equal (out to the zillionth decimal place), is
there any reason I shouldn't violate your Don't and test for that
equality with the = sign?

 

[Joe User]

=IF(A1=A2,"Equal","No")
This should come back saying "Equal" and it does. [....]
=IF(A1-A2=0,"Equal","No")
Since A1 and A2 are equal, subtracting them should get me zero. Yet
this comes back with a "No" in my Excel 2003 SP3.

Weird, huh? What gives?

For all the gory details, see http://support.microsoft.com/kb/78113, notably
the section titled "Example When a Value Reaches Zero". The section is
mistitled, and the description is technically incorrect and factually
incomplete. But it gives a hint as to answer to your question.

The short answer is.... Because of the way that Excel (and most computer
applications) store numbers and perform arithmetic operations, most numbers
with decimal fractions (as well as integers with 16 or more digits) cannot
be stored accurately.

Consequently, for example, IF(10.1-10=0.1,TRUE) is FALSE, and 10.1-10-0.1 is
not exactly zero.

But Excel has implemented some half-baked heuristics that attempt to correct
for such anomalies. They are "half-baked", first, because they are applied
to only particular circumstances, and second, because they create surprises
like the one you stumbled across.

Consequently, using your example, =1.1-0.9-0.2 is exactly zero, but
=(1.1-0.9-0.2) is not.

Rather than try to explain this (although I can, in overwhelming detail), it
might help if you simply concentrate on the work-arounds. None is perfect.

Generally, it is wise to explicitly ROUND the results of any computation
that might result in numbers with decimal fractions, especially
dollars-and-cents. This will ensure (subject to Excel defects) that the
result will have the exact same binary representation as if you had typed
the constant manually.

So returning to your example, A1 should be =ROUND(1.1-0.9,1) instead of
simply =1.1-0.9. Alternatively, =IF(ROUND(A1,1)-ROUND(A2,1)=0,TRUE). Note:
You might choose a different precision for ROUND, depending on your needs.
But it should be close to the number of fractional digits that you expect to
be "accurate".

Alternatively, you might use the calculation option "Precision as Displayed"
(PAD, under Tools > Options > Calculation) to perform an implicit round of
cells that have an explicit numeric format (not General). But PAD can lead
to lots of unexpected results, in part because of its exceptionless
pervasiveness. Also, once you select PAD, some constants might be changed
irreversibly. So be sure to make a copy of your Excel file before you
experiment with PAD.

Finally, you could use ABS() to ensure that the result of a comparison is
within a specified precision or difference. For example,
=IF(ABS(A1-A2-0.2)<0.05,TRUE).

I tried formatting the cells to 30 decimal places

It only makes sense to format up to 15 significant digits. That is the
maximum number of significant digits that Excel will format. The number of
decimal places will depend on the magnitude of the number. In your case,
the maximum useful format is 15 decimal places.

But many people (including the writers of MS KB articles ;->) erroneously
think that means that numbers contain only 15 significant digits and that
arithmetic operations involve only 15 significant digits. That is
incorrect.

For example, 1.1 is 1.10...0 when displayed to 15 significant digits or
more. But it is actually stored as exactly
1.10000000000000,0088817841970012523233890533447265625. Those extra digits
are the source of many "mysterious" results. For example, the result of
=(1.1-1-0.1) -- note the parentheses -- is about 8.3E-17, or exactly
0.0000000000000000832667268468867,405317723751068115234375.

Even that exact result might be surprising. But remember, 0.1 is not
exactly 0.10...0. And that is only part of the explanation in some cases.

Hope that helps. It is only the tip of the iceberg.


----- original message -----

 

[Ron Rosenfeld]

This may be in a FAQ somewhere, but I'm not even sure how to search for
it. It's not really about significant digits, but the subject line's
got to say something. If anyone's interested, see if you can reproduce
the following results in Excel. I'm using Excel 2003 SP3.

It is in the MSKB under the non-intuitive subject of "rounding errors". I
forget the URL but you should be able to find it.

Basically it is related to the IEEE standard for double precision arithmetic,
and also to the impossibility of representing certain decimal numbers in a
finite number of binary digits. (Similar to the impossibility of representing
1/3 exactly in a finite number of decimal digits).

--ron