Why do i sometimes get the answer -1.84741E-13 instead of zero

  • Thread starter Thread starter Cami
  • Start date Start date
C

Cami

It seems to happen when I try to sum two or more cells that are results of
equasions themselves.
 
It's a very small number, but not the same as zero. It's caused by
rounding errors in the intermediate calculations, in the same way that
we might take 0.67 to represent 2/3rds, but obviously 3 * 0.67 does
not give us exactly 2.

You can use the ROUND function to round your calculations to a certain
number of decimal places to help preserve the illusion of accuracy.

Hope this helps.

Pete
 
Cami said:
It seems to happen when I try to sum two or more cells that are results of
equasions themselves.
Click to expand...

(Please repeat and/or expand upon your Subject line in the body of your
message.)

The problem is not limited to summing. And it is not limited to cells that
have formulas, although that tends to exacerbate the problem.

Just for fun, try =IF(10.1 - 10 = 0.1, TRUE). The result is FALSE (!).

The general solution is to use ROUND() prolifically, albeit prudently. For
example, =IF(ROUND(10.1 - 10) = 0.1, TRUE) returns TRUE as expected.

Suppose you have the following formulas:

B1: =A1 * A2
B2: =A3 / A4
B3: =B1 + B2

If all the cells should be computed as dollars and cents, you might consider
doing:

B1: =ROUND(A1 * A2, 2)
B2: =ROUND(A3 / A4, 2)
B3: =ROUND(B1 + B2, 2)

For a long explanation of the issues, see
http://support.microsoft.com/kb/78113 .

In short, numbers with decimal fractions usually cannot be represented
exactly as they appear in the internal form that Excel and most applications
use (binary floating point). Likewise, the results of arithmetic with such
numbers usually cannot be represented exactly. This leads to small
numerical aberrations. Sometimes, Excel tries to "correct" for such
aberrations, which tends to mask the pervasiveness of the problem.

To make matters worse, users are easily fooled by the format of cells, which
often hides additional fractional digits. Note that setting a number of a
decimal places in the cell format only affects how the number is displayed.
It does not alter the actual value of the cell. For example, if a cell with
the value 2.345 is formatted as Number with 2 decimal places, it might
appear as 2.35. But 2 times that cell is (about) 4.69, not 4.70.
 
It's to do with binary: decimal expressions like 0.3 are pretty hard to make
up using the sum of 0.5, 0.25, 0.125, 0.0625... etc

Sam
 
Back
Top