software

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 original
data out to 30 decimal places and I can't figure out where my problem
is. I have the same setup in several places in my spreadsheet and in
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 advance--
JKC
------------------------------------------------------------------------
JKC's Profile: www.excelforum.com/member.php...oamp;userid=31166
View this thread: www.excelforum.com/showthread...hreadid=524576Decimal 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
This is a known issue in excel, see here

support.microsoft.com/kb/78113/en-us--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=524576
This is a known issue in excel, see here

support.microsoft.com/kb/78113/en-us--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=524576Excel is clearly documented (Help for quot;Excel specifications and limitsquot;) to
display no more than 15 decimal digits. To see more you must used the D2D
function at
groups.google.com/group/micro...06871cf92f8465
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.

quot;JKCquot; wrote:

gt;
gt; Here's the deal:
gt; I have 4 cells: cell1 = 53711.83
gt; cell2 = 53711.83
gt; cell3 = 69.12
gt; cell4 = 69.12
gt; When I use a formula = (cell1 cell3-cell2-cell4)/31.25 I should get 0.
gt; Instead I get 0.0000000000000836735125631094.
gt;
gt; Cells 1-4 are based on a dsum. I checked these cells and my original
gt; data out to 30 decimal places and I can't figure out where my problem
gt; is. I have the same setup in several places in my spreadsheet and in
gt; some places the formula works as it should, in a few places it doesn't.
gt; Where are these extra #s coming from? Any Ideas? Thanks in advance
gt;
gt;
gt; --
gt; JKC
gt; ------------------------------------------------------------------------
gt; JKC's Profile: www.excelforum.com/member.php...oamp;userid=31166
gt; View this thread: www.excelforum.com/showthread...hreadid=524576
gt;
gt;


Thanks for everyone's help on this one.

JKC--
JKC
------------------------------------------------------------------------
JKC's Profile: www.excelforum.com/member.php...oamp;userid=31166
View this thread: www.excelforum.com/showthread...hreadid=524576I like your choice of phrase, Jerry - quot; ... does no violence to the
calculations ...quot;

Pete