Suming 2 values with oposite sign, the sum is not 0. but an E val

  • Thread starter Thread starter Veronica
  • Start date Start date
V

Veronica

I multipled some values with "-1" and I was expecting that the sum is 0. But
the sum is an E value, something like 0.000000000000101. For this reason, I
have some errors in a tool I am uploading the excel file, as I need to have
the sum equal 0.
In my file , all figures have maximum 2 decimals, so I do not understand
where the difference is coming from. The problem is not solved if I add the
difference somewhere, as, logicaly, I am adding more decimals that in any
other number in the sum.
 
The simplest solution when adding/subtracting numbers with a fixed number of
decimal places is to round all results to that same number of decimal places.
(Intermediate rounding is usually discouraged, but is advised in this case)

The reason that it occurs is that computers (not just Excel) do arithmetic
in binary. The only 2-decimal place numbers that have exact binary
representation are .00, .25, .50, and .75; the rest must be approximated
(just as 1/3 can only be approximated as a decimal fraction). The
approximation has enough bits to make the 1st 15 significant digits (Excel's
documented display limit) to be what you expected, but the decimal digits
beyond the 15th are usually (except for integers and the few decimal
fractions with exact binary representation) not what you expect. The
subsequent arithmetic is correct, but if you subtract off enough leading
digits then you will reveal residue from the initial approximations.

Jerry
 
Thank you for your answer.

I have tryed to roud up to 2 decimals all the figures, but I still have the
error.
Can I make a computer set up or is there another way not to have difference
of 15 decimals ?
 
You should post your formula(s) with the actual input data. Since
=ROUND(0.000000000000101,2) is 0, not some small number, what I understand
you to say happened is impossible; therefore in the absence of that requested
information, we do not seem to be communicating.

As a simple example of what I suggested, try the following in cells A1:C4
0.3 =A1 =A1
0.1 =(B1-A2) =ROUND(C1-A2,2)
0.1 =(B2-A3) =ROUND(C2-A3,2)
0.1 =(B3-A4) =ROUND(C3-A4,2)
Cell B4 returns -2.8E-17 due to the unavoidable approximations involved in
representing the values 0.3 and 0.1 in binary, with correct math on those
approximated inputs. The intermediate rounding in column C does no violence
to the intended calculation since we are only adding/subtracting numbers with
no more than 2 decimal places.

Jerry
 
Back
Top