Excel adds significant digits, resulting in errors in calculations

[Scoutwert]

I have data from an 8th grade science lab - before and after measurements of
mass. We are plotting a histogram of the differences in mass for a set of
data. However, when I plot the histogram, the results do not match the data,
which is significant to 2 decimal places. Excel says that the result when
you subtract 21.45 from 21.43 is NOT -0.02, but is -0.199999999999996. This
is CRAZY. I know that Excel carries 15 significant digits. However, is
there anything I can do to have Excel perform a simple subtraction of two
numbers?! When did -0.02 become -0.01999999999996?!!
Any help is much appreciated. Using Excel 2003.

 

[Teethless mama]

Format cell to two decimal places

 

[David Biddulph]

Format cell to two decimal places

Format will only change the displayed value. If you want the actual value
to change, you could use =ROUND(A1-A2,2) [if the data were significant only
to 2 decimal places] or anything up to =ROUND(A1-A2,15).

To understand why the answer to the original subtraction doesn't come
through exactly, try to calculate what the exact binary representation of
21.45 will be, or the exact binary representation of 21.43.

 

[Jerry W. Lewis]

If you set the bin boundaries halfway between possible values, then you will
not only get the histogram that you expected, you will also remove the
ambiguity about which bin a particular boundary value goes in.

As for Excel's arithmetic, it is binary, which means that the only 2-digit
decimal fractions that have exact representations are .00, .25, .50, and .75;
the rest must be approximated, just as 1/3 must be approximated as a decimal
fraction.

The decimal representation of the binary approximations to 21.45 and 21.43 are
21.449999999999999289457264239899814128875732421875
21.42999999999999971578290569595992565155029296875
whose difference is
-0.019999999999999573674358543939888477325439453125
Excel performs this calculation exactly and displays the results to its
documented limit of 15 digits.

Since Excel's arithmetic is correct and unexpected results are due to
initial binary approximations to numbers that have no exact binary
representation, when you are simply adding and subtracting numbers of at most
2 decimal places, rounding results to 2 decimal places will return expected
results without violence to the calculations.

Jerry

 

[Scoutwert]

Thank you VERY much. Your explanation is the most informative one I received.
I sure appreciate your time.

Melissa

 

[Scoutwert]

Thanks for your post. I appreciate the time you took to answer my question.
Regards - Melissa

Format cell to two decimal places

Format will only change the displayed value. If you want the actual value
to change, you could use =ROUND(A1-A2,2) [if the data were significant only
to 2 decimal places] or anything up to =ROUND(A1-A2,15).

To understand why the answer to the original subtraction doesn't come
through exactly, try to calculate what the exact binary representation of
21.45 will be, or the exact binary representation of 21.43.

 

[Jerry W. Lewis]

You're welcome. Glad it helped.

Jerry