Wierd result

  • Thread starter Thread starter paulsete
  • Start date Start date
P

paulsete

Hi everybody.

I have the following figures in different columns (A,B,C,D):
27,17.9,8.1,1
In E I have the following formula: -A1+B1+C1+D1. The result should be
0.

However the same it's not, but instead it's 1.77636E-15.
I've solved the issue by doing A1-sum(B1:D1) as I just needed the 0,
however I've got to have the doubt of such weird result.

Thanks to anybody who may answer.
Regards,
Paul
 
If you change B1 to 18 and C1 to 8 (a net result of zero change), your
formula will now show zero as it will with 17.8 and 8.2. Now, set your
format to Number and you will get 0 (zero).

What you are exeriencing is the anomoly of how computers deal with
decimals in a binary world. The actual result of your formula is:

0.00000000000000177636 (pretty insignificant difference for most
applications)

darn those decimals.... :)

HTH

Bruce
 
paulsete wrote...
I have the following figures in different columns (A,B,C,D):
27,17.9,8.1,1
In E I have the following formula: -A1+B1+C1+D1. The result should be
0.

However the same it's not, but instead it's 1.77636E-15.
I've solved the issue by doing A1-sum(B1:D1) as I just needed the 0,
however I've got to have the doubt of such weird result.
Click to expand...
....

Finite precision causes this. Excel, like most other calculation
software, uses binary arithmetic. Most finite *decimal* fractions are
infinite *binary* fractions. That is, just as 1/7 can't be represented
as a finite decimal fraction (it's 0.142857142857... with the 142857
repeating ad infinitem), neither 9/10 not 1/10 can be represented as
finite binary fractions. Truncating those fractions leads to what's
called either rounding, roundoff or truncation error. It's been a
well-known issue in computer programming since the 1950s. The standard
approach to dealing with it is to round your results, e.g.,
ROUND(-A1+B1+C1+D1,6) would return 0.
 
The previous responders have hit the nail on the head. All I have to
add is that the binary approximation to 17.9 is
17.89999999999999857891452847979962825775146484375
Subtracting 27 from it correctly gives
-9.10000000000000142108547152020037174224853515625
The binary approximation to 8.1 is
8.0999999999999996447286321199499070644378662109375
which, when added to the previous result correctly gives
-1.0000000000000017763568394002504646778106689453125
from which your final result is the obvious consequence.

When you do math with approximate inputs, that the output is only
approximate should come as no surprise.

Getting the exact decimal values of the binary approximations is not
trivial in Excel, but you can roughly predict the magnitude by using
Excel's documented limit of 15 decimal digits. Thus you should think of
your problem as
-27
+17.9000000000000???
+8.10000000000000??
+1
--------------------
0.0000000000000???
which is consistent with the actual result
-0.0000000000000017763568394002504646778106689453125

Note that integers are exactly representable in binary, hence there is
no uncertainty with the values of 27 and 1.

Jerry
 
Thank you all.

I'm not that good with all the binary technical explanation, but I
could make a picture with the answers.

Thanks again.
Paul
 
Hello Guys,

I was facing the same problem and I was breaking my head as I was usin
the If statement to check if the result was zero.

I was breaking my head over it for 3 to 4 hrs. :confused:

Great technical explanations guys. The rounding worked.

Thanks :
 
Back
Top