SOLVED

Copper Contributor

 392983.4000000000000 TRUE 392983.4000000000000 11077061.1400000000000 TRUE 11077061.1400000000000 11563581.5300000000000 TRUE 11563581.5300000000000 879503.7900000030000 FALSE 879503.7899999990000 THE ANSWER IN C7 HAVE A 0.000000003 ADDED WITHOUT ANY REASON EVEN THOUGH THE ANSWER IS WRONG THE ANSWER IN C7 IS WRONG

392983.4000000000000

-

11077061.1400000000000

+

11563581.5300000000000

=

879503.7900000030000.     from where did the .000000003 came????

=

879503.7899999990000.     on the right column same from where did the .00999999900 came???

https://we.tl/t-E7c2FvEVs3

2 Replies
best response confirmed by sirajar (Copper Contributor)
Solution

To clarify, what you call C7 is really G7 in the file.  And the corresponding cell on the right is I7.

The simple answer is:  the anomalies are caused by internal binary arithmetic.  In general, the work-around is to round calculations explicitly.  For example:

G7: =ROUND(G3-G4+G5, 2)

I7: =ROUND(I3-I4+I5, 2)

-----

More details; possible TMI....

Most decimal fractions cannot be represented exactly in 64-bit binary floating-point, which Excel and most applications use to represent numbers internally.  Consequently, the decimal fractions must be approximated in binary.

Moreover, the binary approximation of a particular decimal fraction might vary depending on the magnitude of the number.  That is why, for example, 10.1 - 10 = 0.1 returns FALSE(!):  the approximation of 1/10 in 10.1 differs from the approximation of 1/10 in 0.1.

The reason why the sum in G7 differs from the sum I7 is because the value in G5 was calculated, then copy-and-pasted-value, and it is infinitesimally different from (the binary approximation of) the displayed value.

We cannot see the difference because Excel formats only up to the first 15 significant digits (rounded).

But if we enter =SUM(G5,-ROUND(G5,2)) formatted as Scientific, we see that the difference is 3.73E-09 (rounded).  That is 0.00000000373 (rounded).

So, perhaps the calculation of G5 should also be rounded explicitly.

The values in G3 and G4 are the exact binary approximations of the displayed values.

But if they, too, were calculated and copy-and-pasted-value, perhaps those calculations should be rounded explicitly as well, to ensure that future changes don't incur binary arithmetic anomalies.

The choice of when to round explicitly is yours to make.  There are many factors that might influence that decision.