Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extra 1 showing up in 15th decimal place
I'm using Excel 2000 with SR-1. I enter a value of 9.1173 in cell A1, and 9.3213 in B1. I set C1 equal to B1 - A1. I select the entire sheet, format cells as numbers with 4 decimal places. I highlight column C, and conditional format it with two conditions. If the cell value equals .204 the cell should color itself green, if the cell value does not equal .204 the cell should color itself red. The cell is colored red. Expanding the number of decimal places for the entire worksheet shows me a 1 in the fifteenth decimal place of cell C1. This happens for a whole range of values that I'm using. The all should end up being .204 when the subtraction is done, but over half of them are doing the same thing. Additionally, if you enter A1 as 24.324 and B1 as 24.528, the subtraction calculation for C1 will result in .2039999999999970 and it should be .204. Anybody know what's going on here? -- nsc ------------------------------------------------------------------------ nsc's Profile: http://www.excelforum.com/member.php...o&userid=29648 View this thread: http://www.excelforum.com/showthread...hreadid=493565 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extra 1 showing up in 15th decimal place
This the result of the way computers (not just Excel) store numbers. We use
decimal numbers (we have 10 digits), computers use binary numbers (a switch can be on or off). The IEEE convention allows computers to store about 15 decimal places. Some decimal values cannot be exactly represented in this limit so we get 'round-off' errors. Rather than test if (a-b) = 0.204, test if ROUND(a-b,3)=0.204 best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "nsc" wrote in message ... I'm using Excel 2000 with SR-1. I enter a value of 9.1173 in cell A1, and 9.3213 in B1. I set C1 equal to B1 - A1. I select the entire sheet, format cells as numbers with 4 decimal places. I highlight column C, and conditional format it with two conditions. If the cell value equals .204 the cell should color itself green, if the cell value does not equal .204 the cell should color itself red. The cell is colored red. Expanding the number of decimal places for the entire worksheet shows me a 1 in the fifteenth decimal place of cell C1. This happens for a whole range of values that I'm using. The all should end up being .204 when the subtraction is done, but over half of them are doing the same thing. Additionally, if you enter A1 as 24.324 and B1 as 24.528, the subtraction calculation for C1 will result in .2039999999999970 and it should be .204. Anybody know what's going on here? -- nsc ------------------------------------------------------------------------ nsc's Profile: http://www.excelforum.com/member.php...o&userid=29648 View this thread: http://www.excelforum.com/showthread...hreadid=493565 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extra 1 showing up in 15th decimal place
Specifically, the binary approximations (per IEEE 754 standard) to the
numbers in this calculation have decimal values of 9.32130000000000080717654782347381114959716796875 -9.1173000000000001818989403545856475830078125 -------------------------------------------------- 0.20400000000000062527760746888816356658935546875 which is consistent with Excel's result of 0.204000000000001 You can predict the level of binary approximation effects by using Excel's documented limit of 15 digits; think of the problem as 9.32130000000000??? -9.11730000000000??? -------------------- 0.20400000000000??? Jerry Bernard Liengme wrote: This the result of the way computers (not just Excel) store numbers. We use decimal numbers (we have 10 digits), computers use binary numbers (a switch can be on or off). The IEEE convention allows computers to store about 15 decimal places. Some decimal values cannot be exactly represented in this limit so we get 'round-off' errors. Rather than test if (a-b) = 0.204, test if ROUND(a-b,3)=0.204 best wishes |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extra 1 showing up in 15th decimal place
Thanks for the replies concerning the IEE 754 standard. A quick look and it sort of makes sense to me. Where it doesn't make sense is with another set of numbers. 5.3213 minus 5.1173 (note, the decimal numbers are the same as my initial post) works fine in the same circumstances. I would think that if 9.3213 and 9.1173 have binary approximations as Mr. Lewis posted, wouldn't the other pair of numbers have the same approximation? (this is also true if you replace the 5 with a 2, but if you replace it with a 13 it again has the unexpected outcome) I actually have a total of 40 pairs of numbers in my spreadsheet. They all should subtract to .204, and they do. 26 of these pairs end up with this binary approximation error. Thanks again. -- nsc ------------------------------------------------------------------------ nsc's Profile: http://www.excelforum.com/member.php...o&userid=29648 View this thread: http://www.excelforum.com/showthread...hreadid=493565 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extra 1 showing up in 15th decimal place
5.321299999999999918998128123348578810691833496093 75
-5.1173000000000001818989403545856475830078125 ----------------------------------------------------- 0.203999999999999737099187768762931227684020996093 75 You might find my functions at http://groups.google.com/group/micro...fb95785d1eaff5 to be helpful. Jerry nsc wrote: Thanks for the replies concerning the IEE 754 standard. A quick look and it sort of makes sense to me. Where it doesn't make sense is with another set of numbers. 5.3213 minus 5.1173 (note, the decimal numbers are the same as my initial post) works fine in the same circumstances. I would think that if 9.3213 and 9.1173 have binary approximations as Mr. Lewis posted, wouldn't the other pair of numbers have the same approximation? (this is also true if you replace the 5 with a 2, but if you replace it with a 13 it again has the unexpected outcome) I actually have a total of 40 pairs of numbers in my spreadsheet. They all should subtract to .204, and they do. 26 of these pairs end up with this binary approximation error. Thanks again. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extra 1 showing up in 15th decimal place
Intuitively you can see why 5.3213 and 9.3213 would have differeent
binary approximations by thinking about 4-digit decimal approximations to 1/3. 0+1/3 ~ 0.3333 1+1/3 ~ 1.333 9+1/3 ~ 9.333 10+1/3 ~ 10.33 You lose more precision at each power of 10. Similarly the binary approximation to a fraction will lose more precision at each power of 2 (1,2,4,8,...). All nonterminating decimal fractions are nonterminating binary fractions. Most terminating decimal fractions are still nonterminating binary fractions, and hence can only be approximated. The only terminating decimal fractions that are terminating binary fractions are 0.5, 0.25, 0.75, 0.125, 0.375, 0.625, 0.875, 0.0625, ... Jerry nsc wrote: Thanks for the replies concerning the IEE 754 standard. A quick look and it sort of makes sense to me. Where it doesn't make sense is with another set of numbers. 5.3213 minus 5.1173 (note, the decimal numbers are the same as my initial post) works fine in the same circumstances. I would think that if 9.3213 and 9.1173 have binary approximations as Mr. Lewis posted, wouldn't the other pair of numbers have the same approximation? (this is also true if you replace the 5 with a 2, but if you replace it with a 13 it again has the unexpected outcome) I actually have a total of 40 pairs of numbers in my spreadsheet. They all should subtract to .204, and they do. 26 of these pairs end up with this binary approximation error. Thanks again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extract decimal place digit | Excel Worksheet Functions | |||
Custom formatting to force a decimal place | Excel Discussion (Misc queries) | |||
How do I get rid of infinite numbers past the decimal place? | Excel Worksheet Functions | |||
excel validation to 1 decimal place | New Users to Excel | |||
How do I override fixed decimal place settings in EXcel 2003? | Excel Worksheet Functions |