Excel help

[CCTI]

Under Microsoft Office 2000 with Microsoft Excel

In Cell A1, divide 200 by 21.9 (=200/21.9). Answer is correct at 9.12242.
In Cell B1, multiply Cell A1 by 5 (=A1*5). Answer is correct at 45.6621.
In Cell C1, multiply Cell B1 by 21.9 (=B1*21.9). Answer is correct at 1000.
In Cell D1 [=QUOTIENT(C1,100)], I get an incorrect answer of 9 instead of
10.
In Cell E1 [=MOD(C1,100)], I get an incorrect answer of -1.13687E-13.
If I change the 21.9 to 21.8 in Cells A1 and C1, the result in C1 is still
the correct 1000, but now I get correct answers D1 and E1 (10 & 0).
Using 21.7 in A1 & C1, a correct answer of 10 in D1, incorrect answer in E1
(-1.13687E-13).
If I multiply by any whole number 1 thru 30 in B1, I get correct answers for
all numbers except 5, 10, 19, 20 and 29.
Can anyone please explain?

 

[JE McGimpsey]

But the correct answer for

=200/21.9

is

9.132420091324200...

not

9.12242

(though XL only retains 15 significant decimal digits).

Likewise, multiplying A1 * 5 returns

45.6621004566210

rather than

45.6621

So you have some misconceptions already.

There are at least two issues:

1) the displayed number format doesn't change the stored value that
is used in calculations (unless you have the
Tools/Options/Calculation/Precision as displayed checkbox checked, but
that works universally).

2) Most numbers cannot be represented exactly by a finite number of
digits, whether decimal or binary. So real machines need to round the
least significant digits, and sometimes small errors creep in.

See

http://cpearson.com/excel/rounding.htm

for an excellent explanation of (2), and

http://mcgimpsey.com/excel/pennyoff.html

for some possible workarounds.

 

[Jerry W. Lewis]

To elaborate on John's second point, 21.9 is a non-terminating binary
fraction (much as 1/3 is a non-terminating decimal fraction) that can only be
approximated with less than infinite precision.

To intuitively see what happened, imagine a hypothetical digital calculator
that carries only 4 figures. Then
4/(1/3) *2 *(1/3)
=4/0.3333 *2 *0.3333
=12.00 *2 *0.3333
=24.00 *0.3333
=7.999

Excel (like almost all other software) uses 8-bytes as defined by the IEEE
standard to approximate floating point numbers in binary. This IEEE standard
approximation to 21.9 has a decimal value of
21.89999999999999857891452847979962825775146484375. Since Excel will display
no more than 15 significant digits, you cannot directly see the difference
between this and your intended value of 21.9, but you can use the functions at
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465
or a subtraction, such as
=21.9-21.89990234375
to see that what you thought was 21.9 is not exactly.

Consequently, A1 and B1 are slightly larger than you indended. When you
multiply by a number slightly smaller than you intended in C3, you happend to
end up with a number slightly smaller than 1000, specifically
999.9999999999998863131622783839702606201171875
as =(C3-1000) and =MOD(C3,1000) both show.

The approximation to 21.8 is slightly larger than you intended, so these
inequalities are reversed, however it turns out that in C3 when you multiply
a slightly too small number by a slightly too large number, you happen to get
exactly your intended result.

The math is right, given the unavoidable approximations to the input values.
Therefore your calculations must be tolerant of such tiny discrepancies.
Often rounding to a level appropriate to the specific calculation is the
answer.

Jerry

 

[CCTI]

John and Jerry,

Thanks guys, I think I understand. I already knew what numbers I needed to
slightly alter to make my calculations come out looking right, but I didn't
know why I was getting the strange results I was getting when I used the
"correct" or "exact" numbers. I was worried that something was wrong with my
computer. Based on your replies, I now understand that the computer and or
Excel has its limitations and therefore the strange results I was
experiencing was normal. I am assuming that when you two follow my
directions, you get the same strange looking results I get. The difference
being that you understood why it was happening, and I did not.

Thanks again.

To elaborate on John's second point, 21.9 is a non-terminating binary
fraction (much as 1/3 is a non-terminating decimal fraction) that can only be
approximated with less than infinite precision.

To intuitively see what happened, imagine a hypothetical digital calculator
that carries only 4 figures. Then
4/(1/3) *2 *(1/3)
=4/0.3333 *2 *0.3333
=12.00 *2 *0.3333
=24.00 *0.3333
=7.999

Excel (like almost all other software) uses 8-bytes as defined by the IEEE
standard to approximate floating point numbers in binary. This IEEE standard
approximation to 21.9 has a decimal value of
21.89999999999999857891452847979962825775146484375. Since Excel will display
no more than 15 significant digits, you cannot directly see the difference
between this and your intended value of 21.9, but you can use the functions at
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465
or a subtraction, such as
=21.9-21.89990234375
to see that what you thought was 21.9 is not exactly.

Consequently, A1 and B1 are slightly larger than you indended. When you
multiply by a number slightly smaller than you intended in C3, you happend to
end up with a number slightly smaller than 1000, specifically
999.9999999999998863131622783839702606201171875
as =(C3-1000) and =MOD(C3,1000) both show.

The approximation to 21.8 is slightly larger than you intended, so these
inequalities are reversed, however it turns out that in C3 when you multiply
a slightly too small number by a slightly too large number, you happen to get
exactly your intended result.

The math is right, given the unavoidable approximations to the input values.
Therefore your calculations must be tolerant of such tiny discrepancies.
Often rounding to a level appropriate to the specific calculation is the
answer.

Jerry

Under Microsoft Office 2000 with Microsoft Excel

In Cell A1, divide 200 by 21.9 (=200/21.9). Answer is correct at 9.12242.
In Cell B1, multiply Cell A1 by 5 (=A1*5). Answer is correct at 45.6621.
In Cell C1, multiply Cell B1 by 21.9 (=B1*21.9). Answer is correct at 1000.
In Cell D1 [=QUOTIENT(C1,100)], I get an incorrect answer of 9 instead of
10.
In Cell E1 [=MOD(C1,100)], I get an incorrect answer of -1.13687E-13.
If I change the 21.9 to 21.8 in Cells A1 and C1, the result in C1 is still
the correct 1000, but now I get correct answers D1 and E1 (10 & 0).
Using 21.7 in A1 & C1, a correct answer of 10 in D1, incorrect answer in E1
(-1.13687E-13).
If I multiply by any whole number 1 thru 30 in B1, I get correct answers for
all numbers except 5, 10, 19, 20 and 29.
Can anyone please explain?

 

[Jerry W. Lewis]

You're welcome. Glad it helped.

Jerry

John and Jerry,

Thanks guys, I think I understand. I already knew what numbers I needed to
slightly alter to make my calculations come out looking right, but I didn't
know why I was getting the strange results I was getting when I used the
"correct" or "exact" numbers. I was worried that something was wrong with my
computer. Based on your replies, I now understand that the computer and or
Excel has its limitations and therefore the strange results I was
experiencing was normal. I am assuming that when you two follow my
directions, you get the same strange looking results I get. The difference
being that you understood why it was happening, and I did not.

Thanks again.

To elaborate on John's second point, 21.9 is a non-terminating binary
fraction (much as 1/3 is a non-terminating decimal fraction) that can only be
approximated with less than infinite precision.

To intuitively see what happened, imagine a hypothetical digital calculator
that carries only 4 figures. Then
4/(1/3) *2 *(1/3)
=4/0.3333 *2 *0.3333
=12.00 *2 *0.3333
=24.00 *0.3333
=7.999

Excel (like almost all other software) uses 8-bytes as defined by the IEEE
standard to approximate floating point numbers in binary. This IEEE standard
approximation to 21.9 has a decimal value of
21.89999999999999857891452847979962825775146484375. Since Excel will display
no more than 15 significant digits, you cannot directly see the difference
between this and your intended value of 21.9, but you can use the functions at
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465
or a subtraction, such as
=21.9-21.89990234375
to see that what you thought was 21.9 is not exactly.

Consequently, A1 and B1 are slightly larger than you indended. When you
multiply by a number slightly smaller than you intended in C3, you happend to
end up with a number slightly smaller than 1000, specifically
999.9999999999998863131622783839702606201171875
as =(C3-1000) and =MOD(C3,1000) both show.

The approximation to 21.8 is slightly larger than you intended, so these
inequalities are reversed, however it turns out that in C3 when you multiply
a slightly too small number by a slightly too large number, you happen to get
exactly your intended result.

The math is right, given the unavoidable approximations to the input values.
Therefore your calculations must be tolerant of such tiny discrepancies.
Often rounding to a level appropriate to the specific calculation is the
answer.

Jerry

Under Microsoft Office 2000 with Microsoft Excel

In Cell A1, divide 200 by 21.9 (=200/21.9). Answer is correct at 9.12242.
In Cell B1, multiply Cell A1 by 5 (=A1*5). Answer is correct at 45.6621.
In Cell C1, multiply Cell B1 by 21.9 (=B1*21.9). Answer is correct at 1000.
In Cell D1 [=QUOTIENT(C1,100)], I get an incorrect answer of 9 instead of
10.
In Cell E1 [=MOD(C1,100)], I get an incorrect answer of -1.13687E-13.
If I change the 21.9 to 21.8 in Cells A1 and C1, the result in C1 is still
the correct 1000, but now I get correct answers D1 and E1 (10 & 0).
Using 21.7 in A1 & C1, a correct answer of 10 in D1, incorrect answer in E1
(-1.13687E-13).
If I multiply by any whole number 1 thru 30 in B1, I get correct answers for
all numbers except 5, 10, 19, 20 and 29.
Can anyone please explain?