Ever come across (0.00)?

L

LavaDude

I don't know why excel is doing this... Any help (or explanation) would be
appreciated...

I have the following calculations:

D19 = 374,268.89
E19 = =ROUND(D19*0.07,2)
F19 = [left blank]
G19 = =-1881.75*12
H19 = =SUM(E19:G19)

I19 = 3617.74
J19 = =IF(H19<0,-I19,H19-I19)-0.08

FYI: Cell F19 is blank

Basically, H19 is showing $3,617.82 and I19 is $3,617.74, so the "-0.08" at
the end of the formula in J19 should make the calculation 0.00 ... But it's
showing "(0.00)" ... and when I increase the decimal places I see
(0.000000000000073) ... What's up with that? ...

The only cell that would cause rounding problems has been rounded to two
(2) decimal places (cell E19) ... Why would excel cause this type of
rounding to the 14th (or 15th) decimal place?

To solve the "(0.00)" ... I just added the round formula to J19, but I'm
wondering why excel does this...

TIA!!! ...

LavaDude...
 
J

Jerry W. Lewis

Eliminating all the extraneous information (always a good idea when
posting a question), your concern is that
=26198.82-22581-3617.74-0.08
returns -7.27612414763712E-14 instead of zero.

Excel's math is correct, but most of your input values had to be
approximated, leading to an approximate final result.

Excel (and almost all other software) does math in binary, not decimal.
Most decimal fractions (including .82, .74, and .08) have no exact
binary representation (just as 1/3 has no exact decimal representation)
and hence must be approximated. The approximations used by Excel (and
almost all other software) are those defined by the IEEE 754 standard,
which is discussed at
http://www.cpearson.com/excel/rounding.htm

To see what has happened, consider a hypothetical decimal computer that
carries only 4 decimal figures
=((123+1/3)-123)-1/3
would be calculated as
=(123.3-123)-0.3333
=0.3-0.3333
=-0.0333
instead of zero. As you can see the issue is that finite precision
necessarily implies that approximations to different sized numbers will
be to a different number of decimal places. The same thing is happening
in your problem, except in binary (and with more precision).

It is not obvious in decimal that the following numbers are simpler
approximations in binary, but the exact numbers that Excel sees for your
problem are
26198.8199999999997089616954326629638671875
-22581
- 3617.739999999999781721271574497222900390625
- 0.08000000000000000166533453693773481063544750213623046875
--------------------------------------------------------------
-0.00000000000007276124147637119676801376044750213623046875
which Excel reports to its documented limit of 15 decimal figures as
-0.0000000000000727612414763712

Decimal-binary floating point conversions are not simple, but you can
use that documented 15 figure limit as a simple way to predict the
potential magnitude of binary approximations. Think of your problem as
26198.8200000000???????
-22581
- 3617.74000000000??????
- 0.0800000000000000?
-----------------------
-0.0000000000???????

Since the issue is approximation of inputs, you can judiciously use
rounding to hide the impact of those approximations. In particular, for
your problem, you are adding and subtracting with nothing beyond the 2nd
decimal place, therefore anything beyond the 2nd decimal place is junk
from binary approximations.
=ROUND(26198.82-22581-3617.74-0.08,2)
then does no violence to your calculation and returns the result you
expected.
I don't know why excel is doing this... Any help (or explanation) would be
appreciated...

I have the following calculations:

D19 = 374,268.89
E19 = =ROUND(D19*0.07,2)
F19 = [left blank]
G19 = =-1881.75*12
H19 = =SUM(E19:G19)

I19 = 3617.74
J19 = =IF(H19<0,-I19,H19-I19)-0.08

FYI: Cell F19 is blank

Basically, H19 is showing $3,617.82 and I19 is $3,617.74, so the "-0.08" at
the end of the formula in J19 should make the calculation 0.00 ... But it's
showing "(0.00)" ... and when I increase the decimal places I see
(0.000000000000073) ... What's up with that? ...

The only cell that would cause rounding problems has been rounded to two
(2) decimal places (cell E19) ... Why would excel cause this type of
rounding to the 14th (or 15th) decimal place?

To solve the "(0.00)" ... I just added the round formula to J19, but I'm
wondering why excel does this...

TIA!!! ...

LavaDude...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help! Getting different results for the same formula. 1
Looking for more elegant code 4

Top