Formula error

[Dee]

I have a simple calculation C8-C14 that I use to figure my budget
difference. It works in all the monthly columns I have set up except for
one. I get a result of

-1.42109E-13

What could I have done wrong?????

 

[Ken Wright]

Probably nothing.

See here for the probable cause:

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

Ignore the fact that the page says Excel 97, as you aill also see in the
small print it actually applies to all versions of Excel.

If you simply format to acctg or some numerical format and a few decimal
places of precision, it will appear as 0.

Regards
Ken...............................

 

[Ken Wright]

If you need it to truly be 0, use the ROUND() function on your formula, eg

=ROUND(C8-C14,2) rounds to 2 decimal places
=ROUND(C8-C14,3) rounds to 3 decimal places
=ROUND(C8-C14,4) rounds to 4 decimal places

Regards
Ken....................

 

[Dee]

Thanks much!!! I formated the cell to "numbers" and it gave the correct
answer. Kinda wierd that the other cells are "general" and work fine....


Anyway................you have taught me a new tool and fixed the problem

 

[JoeU2004]

I have a simple calculation C8-C14
I get a result of -1.42109E-13
What could I have done wrong?

Nothing really, except: you probably should be rounding results. At a
minimum, ROUND(C8-C14,2). But if C8 or C14 contains a formula, not a
constant, you will probably stumble onto fewer problems if you do
=ROUND(formula,2) in each of those. Apply the same recommendation to all
cells with formulas.

The problem has to do with the fact that in Excel, most numbers with decimal
fractions cannot be represented exactly. For example, 100.01 (A1) is
actually estimated internally by
100.010000000000,0051159076974727213382720947265625, and 0.01 (A2) is
0.0100000000000000,0020816681711721685132943093776702880859375. But A1-100
(A3) results in a different approximation of 0.01, namely
0.0100000000000051,159076974727213382720947265625. (The comma is my way of
demarcating 15 significant digits to the left).

You can see that the two estimations of 0.01 are different. And in this
case, A2-A3 (A4) results in about 5.1157E-15.

But if A3 were =ROUND(A1-100,2), the resulting approximation of 0.01 would
be the same as 0.01. Nonetheless, to be safe, I would still compute
ROUND(A2-A3,2). That ensures that "what you see is what you get".

Alternatively, you could set the calculation option "Precision as displayed"
(Tools > Options > Calculation). But that affects all cells in the workbook
that are not formatted General. I do not recommend it.

It is important to understand that formatting the cells as Number with 2
decimal places does not change the underlying values. Consequently, it does
not fix the problem unless you set "Precision as displayed".

Moreover, you should make prudent decision when __not__ round. For example,
if annual interest is 4.5%, I calculate monthly interest as 4.5%/12 without
rounding.


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

 

[JoeU2004]

I formated the cell to "numbers" and it gave the correct answer.

Generally not the case. In fact, explicit formatting is often the source of
the confusion. You see numbers rounded to 2 decimal places, but in fact the
underlying values are not. Unless you set the option "Precision as
displayed" option.

Kinda wierd that the other cells are "general" and work fine

I hope my other response gives some insight into that. In a nutshell, it is
all coincidental. Some decimal fractions (e.g. 0.50 and 0.25) can be
represented exactly. Excel heuristics try to "correct" the numerical
anomalies under specific conditions. And sometimes, the result depends on
the order of operations; that is, A+B-C might not have exactly the same
result as A-C+B.

Suffice it to say, it can all seem quite mysterious and unpredictable.


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

 

[Ken Wright]

Do be aware that the cell does not actually EQUAL 0, it simply APPEARS to be
so. You need to use something along the lines of both mine and Joe's
answers such as the ROUND function if you actually need it to really be an
absolute 0.

Regards
Ken.........................