Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
order of calculations in Excel | Excel Worksheet Functions | |||
Calculations run slow in Excel 2002 SP-1 | Excel Discussion (Misc queries) | |||
Are there any significant differences in Excel 2003 from Excel 20. | Excel Discussion (Misc queries) | |||
Excel 2000 file when opened in Excel 2003 generates errors? | Excel Discussion (Misc queries) |