error in simple calculation?

[Tobias]

Hello everybody

I have a small problem...

I have entered 2.5% in A1, 100% in A2 ans 100 in A3.

In A4 I have the following formula: =A3*(1+$A$1)-(A3*$A$1) with the
result 100.
(In fact I calculate an interest on a certain amount and just after I
withdraw the interest)

Now I copy this formular in the next 20 cells beneath A4 and I recieve
the following results:

A5: 100
A6: 100
A7: 99.999999999999900 ????
A8: 99.999999999999900
A9: 99.999999999999900
A10: 99.999999999999900
A11: 99.999999999999900
A12: 99.999999999999900
A13: 99.999999999999900
A14: 99.999999999999800 ????
A15: 99.999999999999800
A16: 99.999999999999800
A17: 99.999999999999800
A18: 99.999999999999800
A19: 99.999999999999800
A20: 99.999999999999800
A21: 99.999999999999700 ????
A20: 99.999999999999700

and so on (this value remains).

Shouldn't the result of this calculation always be exactly "100"?

What do I do wrong? Thank you very much for any help.

best regards, Tobias

 

[Guest]

You have not done anything wrong. this is just Excel's way of handling
calculations when referring to a previous cell which contains the same
calculation. Interesting, mine (On XP) gets as far as 99.999999999999800,
and then sits there.

 

[Jerry W. Lewis]

Excel's math is correct, but your inputs must be approximated. You
would find the same issue with almost all other programs as well.

Almost all software (Excel included) does binary math. Most decimal
fractions (including 0.025 = 2.5%) have no exact finite binary
representation (much as 1/3 has no exact finite decimal representation),
and hence must be approximated. When you use approximations as inputs,
it should be no surprise that the answers are also only approximate.

As a result of these approximations, A4 is not exactly 100, ... The formula
=(A4-100)
will reveal this discrepancy. Note that the parentheses are required
for Excel to show the discrepancy, since the numbers are equal to
Excel's documented limit (based on the IEEE 754 standard for double
precision binary representation) of 15 decimal digits.

The trailing zeroes in A7:A20 are meaningless, because Excel (as
documented) will not show you more than 15 digits even if you ask it to.

If you consider that there is possibly binary junk beyond the 15th
figure on every floating point number you use, then you can chase
through the potential magnitude of binary approximations in your
calculations. Think of 2.5% as
0.0250000000000000????
the 100 in A4 as
100.000000000000???
etc.

In practice, the bank is unlikely to credit you fractions of penny's in
interest, and even if it did, it would have no way to allow you to
withdraw fractions of a penny. Thus you could without violence round
all calculated results to 2 decimal places and thereby prevent the
accumulation of approximation discrepancies.

Alternately, you could restructure your calculations so that all inputs
and outputs are integers, where no approximations are required.

Jerry

 

[Tobias]

Excel's math is correct, but your inputs must be approximated. You
would find the same issue with almost all other programs as well.

Almost all software (Excel included) does binary math. Most decimal
fractions (including 0.025 = 2.5%) have no exact finite binary
representation (much as 1/3 has no exact finite decimal representation),
and hence must be approximated. When you use approximations as inputs,
it should be no surprise that the answers are also only approximate.

As a result of these approximations, A4 is not exactly 100, ... The formula
=(A4-100)
will reveal this discrepancy. Note that the parentheses are required
for Excel to show the discrepancy, since the numbers are equal to
Excel's documented limit (based on the IEEE 754 standard for double
precision binary representation) of 15 decimal digits.

The trailing zeroes in A7:A20 are meaningless, because Excel (as
documented) will not show you more than 15 digits even if you ask it to.

If you consider that there is possibly binary junk beyond the 15th
figure on every floating point number you use, then you can chase
through the potential magnitude of binary approximations in your
calculations. Think of 2.5% as
0.0250000000000000????
the 100 in A4 as
100.000000000000???
etc.

In practice, the bank is unlikely to credit you fractions of penny's in
interest, and even if it did, it would have no way to allow you to
withdraw fractions of a penny. Thus you could without violence round
all calculated results to 2 decimal places and thereby prevent the
accumulation of approximation discrepancies.

Alternately, you could restructure your calculations so that all inputs
and outputs are integers, where no approximations are required.

Jerry

Hallo Kassie, hello Jerry

Thank you very much for your answers. This is amazing. Now I've been
working for years with this program and I still don't understand it.
Besides never heard of binary math, but anyway, you both have helped
me. Jerry, with your precise and rational information you gave me food
for my brain. Kassie, with your understanding you gave me food for my
heart. Be blessed.

Tobias

 

[Jerry W. Lewis]

Hallo Kassie, hello Jerry

Thank you very much for your answers. This is amazing. Now I've been
working for years with this program and I still don't understand it.
Besides never heard of binary math, but anyway, you both have helped
me. Jerry, with your precise and rational information you gave me food
for my brain. Kassie, with your understanding you gave me food for my
heart. Be blessed.

Tobias

You're welcome. Glad it helped.

Jerry