Excel adds significant digits, resulting in errors in calculations
 

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.

Excel adds significant digits, resulting in errors in calculations
 

Format cell to two decimal places

"Scoutwert" wrote:

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.

Excel adds significant digits, resulting in errors in calculations
 

"Teethless mama" wrote in message
...
"Scoutwert" wrote:

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.

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.
--
David Biddulph

Excel adds significant digits, resulting in errors in calculations
 

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.44999999999999928945726423989981412887573242187 5
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" wrote:

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.

Excel adds significant digits, resulting in errors in calculat
 

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

Melissa

"Jerry W. Lewis" wrote:

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.44999999999999928945726423989981412887573242187 5
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" wrote:

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.

Excel adds significant digits, resulting in errors in calculat
 

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

"David Biddulph" wrote:

"Teethless mama" wrote in message
...
"Scoutwert" wrote:

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.

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.
--
David Biddulph

Excel adds significant digits, resulting in errors in calculat
 

You're welcome. Glad it helped.

Jerry

"Scoutwert" wrote:

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

Melissa