Excel Basic Math Spreadsheet Calculation Error?

[S. Reiman]

I obtained a simple basic math error with four cell
references, =Cell1-Cell2-Cell3-Cell4 returning the result
of 4.44089E-16, displayed in either general or scientific
format. The only calculated cell was the final formula
cell, the contents are below.

Cell1 was 10
Cell2 was 1.28
Cell3 was 5.90
Cell4 was 2.82

Placing parenthesis =Cell1-(Cell2+Cell3+Cell4) and summing
before subtracting provides the correct answer, zero.

Is this an Excel error (version 2002 10.4302.4219 SP-2)

or a computer computational limitation?

I was not aware that it was so easy to obtain an error
with Excel.

 

[JE McGimpsey]

Hmm.. I don't get the same results in XLv.X, but fundamentally, there's
a computational limitation.

See

http://cpearson.com/excel/rounding.htm

 

[Ron Rosenfeld]

I obtained a simple basic math error with four cell
references, =Cell1-Cell2-Cell3-Cell4 returning the result
of 4.44089E-16, displayed in either general or scientific
format. The only calculated cell was the final formula
cell, the contents are below.

Cell1 was 10
Cell2 was 1.28
Cell3 was 5.90
Cell4 was 2.82

Placing parenthesis =Cell1-(Cell2+Cell3+Cell4) and summing
before subtracting provides the correct answer, zero.

or a computer computational limitation?

I was not aware that it was so easy to obtain an error
with Excel.

I could not reproduce that error with Excel 2002 (10.4524.4219) SP-2

However, if your "final formula cell" is Cell4, that could explain our
differences.

In any event, Excel, conforming to IEEE standards, has fifteen digits of
precision. And there are some numbers that cannot be represented exactly in
binary notation. These facts combine to produce a "computational limitation".


--ron

 

[Jerry W. Lewis]

=Cell1-Cell2-Cell3-Cell4
returns zero (I believe in all versions of Excel), however
=(Cell1-Cell2-Cell3-Cell4)
returns 4.44089E-16. It is not an error, and it is not unique to Excel.

The latter result is the exact answer to an approximate problem. As Ron
Rosenfeld suggested, the issue is binary arithmetic. None of the values
in Cell2, Cell3, or Cell4 can be exactly represented in binary (which is
used by almost all general purpose computing software). Excel then does
exact arithmatic on the approximate inputs to get 4.44089E-16, which is
well within the advertised 15 figure accuracy.

The reason that the first form gets zero is not that the math is done
differently. Instead, when the final operation is a subtraction where
the result is zero to 15 figures, Excel arbitrarily zeroes the nonzero
result to try to avoid these kinds of questions. The second form
bypasses that fuzz factor because of the parenteses.

Your third equation
=Cell1-(Cell2+Cell3+Cell4)
still has to approximate the values in Cells 2 through 4, but the answer
to the approximate problem happens to be the same as the answer to your
decimal problem to the available precision.

Jerry