ExcelBanter - Does Excel calculate wrong when using many decimal after comma?

On Dec 7, 8:56*am, nankerp wrote:
I have put 1592,10 in cells A1 to A57 and made a SUM(A1:A57)
in B57. The result is 90749,70000000010. That is wrong. I[t]
should be 90749,7000000000000.

More to the point, =57*A1 does not equal =SUM(A1:A57), which might
seem even stranger to some people.

The answer is: Excel and most applications rely on binary
representation and arithmetic. Consequently, most decimal fractions
cannot be represented exactly. For example, 1592.10 is represented
internally exactly as 1592.09999999999,990905052982270717620849609375.

(Note: I am using a comma to show the 15th significant digit to the
left, which is different from your country's use of commas.)

Generally, most binary computer arithmetic propagates such numerical
"errors" (not really errors; just different from expectations). For
example, =SUM(A1:A57) results in exactly
90749.7000000000,55297277867794036865234375. In contrast, the
internal representation of 90749.7 is exactly
90749.6999999999,97089616954326629638671875. Close; but not exactly
the same.

Coincidently, =57*A1 does result in exactly the same internal
representation as 90749.7. But I emphasize that that is coindence.
The point is, though: addition of a number N times might not equal
that number times N because of other vagaries of computer binary
arithmetic. More generally, two different formulations that might be
equal mathematically are frequently not equal when performed by
computer arithmetic.

Excel does have some heuristics to try to ameliorate these anomalies.
But they do not work in all cases, as evidenced by your observations.

When working with decimal fractions, even simple financial numbers, it
is prudent either to use the Precision As Displayed calculation option
(which I deprecate), or to use the ROUND() function liberally.

For example, I would write =ROUND(A1*57,2).

I would also write =ROUND(SUM(A1:57),2). But there is an important
caveat: when rounding a long sequence of arithmetic operations
(A1+A2+...+A57), you might not always get the expected result. It
works in this case because the accumulated numerical "error" is small
relative to the number of decimal place. (Actually, relative to the
number of significant digits.)

Consequently, when comparing results with very many significant
digits, it is prudent not to test for equality. Instead, either test
for "=" or "<=", or test for a difference within a small range, which
only you can decide (e.g. abs(A1-A2) <= 1e-6). Usually, this
complication is unnecessary in financial computations, if you use ROUND
() liberally. But it does depend on the relative magnitudes of your
numbers.