calculation issue?

[JKC]

Here's the deal:
I have 4 cells: cell1 = 53711.83
cell2 = 53711.83
cell3 = 69.12
cell4 = 69.12
When I use a formula = (cell1+cell3-cell2-cell4)/31.25 I should get 0
Instead I get 0.0000000000000836735125631094.

Cells 1-4 are based on a dsum. I checked these cells and my origina
data out to 30 decimal places and I can't figure out where my proble
is. I have the same setup in several places in my spreadsheet and i
some places the formula works as it should, in a few places it doesn't
Where are these extra #s coming from? Any Ideas? Thanks in advanc

 

[Pete_UK]

Decimal fractions don't always convert easily to binary. Some fractions
like 1/3 cannot be expressed exactly in decimal. Therefore, small
rounding errors creep in to some formulae, and yours is one such
example. Try rounding the results of your dsum to 2 dp using ROUND( )
and see if this helps matters.

Hope this helps.

Pete

 

[daddylonglegs]

This is a known issue in excel, see here

http://support.microsoft.com/kb/78113/en-u

 

[daddylonglegs]

This is a known issue in excel, see here

http://support.microsoft.com/kb/78113/en-u

 

[Jerry W. Lewis]

Excel is clearly documented (Help for "Excel specifications and limits") to
display no more than 15 decimal digits. To see more you must used the D2D
function at
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465
Using that, you will see that neither 53711.83 nor 69.12 can be represented
exactly.

This issue common to all software that does binary math (almost all
software). When you do math with approximate inputs, it should be no
surprise when the output is also only approximate.

What you have observed is the binary equivalent of
1-(1/3)-(1/3)-(1/3) = 1-0.3333-0.3333-0.3333 = 0.0001
The math is right, but the answer is not the expected zero because of
necessary initial approximations to 1/3, which cannot be represented exactly
as a decimal fraction in finite precision.

Most terminating decimal fractions (including .83 and .12) are
non-terminating binary fractions ...

Excel's documented 15-digit limit can be used to predict the magnitude of
approximation issues. Your problem can then be thought of as
53711.8300000000????
69.1200000000000?
-53711.8300000000????
-69.1200000000000?
---------------------
0.0000000000????
which is consistent with Excel's answer
0.00000000000261
(the final division was irrelevant to the basic issue and hence was omitted
here)

Since the issue is approximation to inputs, not subsequent math, Pete's
rounding suggestion is entirely reasonable, and does no violence to the
calculations.

 

[JKC]

Thanks for everyone's help on this one.

JK

 

[Pete_UK]

I like your choice of phrase, Jerry - " ... does no violence to the
calculations ..."

Pete