Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this a bug in excel?
I have the following formula in excel:
=IF((F157+G157)0,IF(B157<"#",(L156+G157-F157),L156),0) The first two IFs evaluate to true so the result is basicaly L156+G157-F157 and the values are (58,511.55 + 86.66 - <empty cell) [The 58k value is itself a result of another formula like the above] You would expect 58,598.21 but instead excel gives 58,598.210000000100000 Setting up three additional formulas that are just an equals on the two data cells and the result gives the following: 58,511.55000000000000000 86.6600000000000000000 58,598.210000000100000000 Doing an 'Evaluate Formula' gives IF(TRUE,IF(TRUE,(58511.55+86.66-F157),L156),0) and then IF(TRUE,IF(TRUE,(58,598.2100000001-F157),L156),0) AAARRRRGGGHHHHH!!!!! What is going on. I don't see how this can be anything other than a bug. I have searched the MS site to look for a way of reporting a bug but there doesn't seem to be one. Anyone got any ideas on this probelm or know how to report a bug Changing the 86.66 to .65 or .67 removes the 1 at the 10th decimal place. I can work around it by using two rows in my sheet to make up the 86.66 transaction but if this keeps happening it is going to be very annoying. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this a bug in excel?
Hi,
Follow the link: http://support.microsoft.com/kb/78113/en-us "Mister_T" a écrit dans le message de ... I have the following formula in excel: =IF((F157+G157)0,IF(B157<"#",(L156+G157-F157),L156),0) The first two IFs evaluate to true so the result is basicaly L156+G157-F157 and the values are (58,511.55 + 86.66 - <empty cell) [The 58k value is itself a result of another formula like the above] You would expect 58,598.21 but instead excel gives 58,598.210000000100000 Setting up three additional formulas that are just an equals on the two data cells and the result gives the following: 58,511.55000000000000000 86.6600000000000000000 58,598.210000000100000000 Doing an 'Evaluate Formula' gives IF(TRUE,IF(TRUE,(58511.55+86.66-F157),L156),0) and then IF(TRUE,IF(TRUE,(58,598.2100000001-F157),L156),0) AAARRRRGGGHHHHH!!!!! What is going on. I don't see how this can be anything other than a bug. I have searched the MS site to look for a way of reporting a bug but there doesn't seem to be one. Anyone got any ideas on this probelm or know how to report a bug Changing the 86.66 to .65 or .67 removes the 1 at the 10th decimal place. I can work around it by using two rows in my sheet to make up the 86.66 transaction but if this keeps happening it is going to be very annoying. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this a bug in excel?
"Mister_T" wrote:
AAARRRRGGGHHHHH!!!!! What is going on. I don't see how this can be anything other than a bug. I have searched the MS site to look for a way of reporting a bug but there doesn't seem to be one. Anyone got any ideas on this probelm or know how to report a bug No, this is not a defect. It is a side-effect of the way that Excel (and most applications) do arithmetic on binary computers. But before I get into my explanation, here some pointers to Microsoft's: http://support.microsoft.com/kb/78113 http://support.microsoft.com/kb/42980 http://support.microsoft.com/kb/214118 (58,511.55 + 86.66 - <empty cell) [The 58k value is itself a result of another formula like the above] You would expect 58,598.21 but instead excel gives 58,598.210000000100000 Your parenthetical comment (58511.55 is the result of a formula) is the key. In this particular case, we cannot duplicate your results simply by typing (58511.55 + 86.66 - 0), with or without the parentheses, which surprisingly can make a difference sometimes. In your case, the expression does not equal 58598.21 "exactly" (i.e. within 15 significant digits) because 58.511.55 and/or 86.66 are not "exactly" those values within 15 significant digits. Format each cell as Scientific with 14 decimal places, and you should see the disparity. (If you are unfamiliar with Scientific notation, don't worry. It is just a consistent way of seeing 15 significant digits regardless of the magnitude of the number.) What you probably really want to know is: how do you avoid this very common anomaly? The answer is: use ROUND judicious and, IMHO, prolifically. For example, ROUND(A1+A2-A3,2), where A1 has the formula that results in 58511.55, A2 has the formula that results in 86.66, and A3 is the empty cell. (Note: Another alternative is to set the "Precision as displayed" option under Tools Options Calcuation. I do not recommend it for some very specific reasons. If you are interested, post a response in this thread, and I will explain.) Since I cannot duplicate your example, lacking the exact values, I'll use my favorite examples to explain. Consider 10.1 - 10 in A1 and 0.1 in A2. =(A1=A2) is FALSE, and =A1-A2 is -3.608E-16. In a nutshell, this is because numbers are represented internally by 53 consecutive powers of 2 ("bits"). With 0.1, we can use the full 53 bits to represent 0.1. But with 10.1, some of the bits are used to represent 10; so there are fewer bits to represent 0.1. In this case, that results in a different representation of 0.1 as part of 10.1. When we subtract 10, we are left with this different representation. This might be clearer if you could see the exact decimal representation of the internal values. Excel will not do that; it limits itself to presenting only the first 15 significant digits. But the exact internal value of 10.1 is 10.0999999999999,996447286321199499070644378662109 375; the exact internal value of 10.1 - 0.1 is 0.0999999999999996,4472863211994990706443786621093 75; and the exact internal value of 0.1 is 0.100000000000000,00555111512312578270211815834045 41015625. (The comma is my way of demarcating the first 15 significant digits to the left.) The exact internal values of 10.1 - 0.1 and 0.1 are not close enough for Excel to consider them equal. Note the words "close enough". Excel has implemented some heuristics to consider two different internal values as equal under some very narrow conditions. Unfortunately, those heuristics often add to the confusion, in part because they are poorly defined, IMHO. For example, consider 0.1 + 2^-56 in A1 (just slightly larger than 0.1) and 0.1 in A2. =(A1=A2) returns TRUE even though the internal values obviously are not equal. Likewise, =A1-A2 returns zero. But =(A1-A2) returns non-zero, namely about 1.39E-17; note that the only difference is the parentheses. And for the same reason, =IF(A1-A2=0,TRUE) returns FALSE, which flies in the face of reason considering the result of =A1-A2. The latter example is explained somewhat if you read between the lines under the heading "Example When a Value Reaches Zero" on the web page at http://support.microsoft.com/kb/78113 . The explanation is flawed(!); but it is the best that Microsoft has to offer. You would not like my more-precise explanation, which I reverse-engineered. It is really too techy. (But if you really want to know, ask for it in a response in this thread.) Hope this helps. I would be happy to go into more detail if you have questions. It might be helpful if you posted the Scientific format (with 14 dp) of the numbers in question. But bear in mind that even that is sometimes not good enough for us to see the difference; consider my example of 0.1 + 2^-56. At the very least, I hope the "short" explanation above demonstrates that this is not a defect per se. ----- original message ----- "Mister_T" wrote in message ... I have the following formula in excel: =IF((F157+G157)0,IF(B157<"#",(L156+G157-F157),L156),0) The first two IFs evaluate to true so the result is basicaly L156+G157-F157 and the values are (58,511.55 + 86.66 - <empty cell) [The 58k value is itself a result of another formula like the above] You would expect 58,598.21 but instead excel gives 58,598.210000000100000 Setting up three additional formulas that are just an equals on the two data cells and the result gives the following: 58,511.55000000000000000 86.6600000000000000000 58,598.210000000100000000 Doing an 'Evaluate Formula' gives IF(TRUE,IF(TRUE,(58511.55+86.66-F157),L156),0) and then IF(TRUE,IF(TRUE,(58,598.2100000001-F157),L156),0) AAARRRRGGGHHHHH!!!!! What is going on. I don't see how this can be anything other than a bug. I have searched the MS site to look for a way of reporting a bug but there doesn't seem to be one. Anyone got any ideas on this probelm or know how to report a bug Changing the 86.66 to .65 or .67 removes the 1 at the 10th decimal place. I can work around it by using two rows in my sheet to make up the 86.66 transaction but if this keeps happening it is going to be very annoying. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this a bug in excel?
Hi,
Computers work in binary, we work in decimals which results in approximations by Excel and any computer. Here is everything you need to know about this issue (and more): http://support.microsoft.com/kb/78113/en-us http://support.microsoft.com/kb/42980 http://support.microsoft.com/kb/214118 http://www.cpearson.com/excel/rounding.htm http://docs.sun.com/source/806-3568/ncg_goldberg.html -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Mister_T" wrote: I have the following formula in excel: =IF((F157+G157)0,IF(B157<"#",(L156+G157-F157),L156),0) The first two IFs evaluate to true so the result is basicaly L156+G157-F157 and the values are (58,511.55 + 86.66 - <empty cell) [The 58k value is itself a result of another formula like the above] You would expect 58,598.21 but instead excel gives 58,598.210000000100000 Setting up three additional formulas that are just an equals on the two data cells and the result gives the following: 58,511.55000000000000000 86.6600000000000000000 58,598.210000000100000000 Doing an 'Evaluate Formula' gives IF(TRUE,IF(TRUE,(58511.55+86.66-F157),L156),0) and then IF(TRUE,IF(TRUE,(58,598.2100000001-F157),L156),0) AAARRRRGGGHHHHH!!!!! What is going on. I don't see how this can be anything other than a bug. I have searched the MS site to look for a way of reporting a bug but there doesn't seem to be one. Anyone got any ideas on this probelm or know how to report a bug Changing the 86.66 to .65 or .67 removes the 1 at the 10th decimal place. I can work around it by using two rows in my sheet to make up the 86.66 transaction but if this keeps happening it is going to be very annoying. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this a bug in excel?
Shane Devenshire wrote...
Computers work in binary, we work in decimals which results in approximations by Excel and any computer. .... Any computer? You're apparently unaware that there are decimal-based digital computers. Heck, old mechanical adding machines were decimal computers. And not by any software either. There's arbitrary precision software that handles decimal arithmetic. What you should have written was Excel and any other software that uses binary floating point arithmetic. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this a bug in excel?
Sound like Harlan once programmed in COBOL where decimal math is the default
cheers -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Harlan Grove" wrote in message ... Shane Devenshire wrote... Computers work in binary, we work in decimals which results in approximations by Excel and any computer. ... Any computer? You're apparently unaware that there are decimal-based digital computers. Heck, old mechanical adding machines were decimal computers. And not by any software either. There's arbitrary precision software that handles decimal arithmetic. What you should have written was Excel and any other software that uses binary floating point arithmetic. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this a bug in excel?
PS....
"Mister_T" wrote: =IF((F157+G157)0,IF(B157<"#",(L156+G157-F157),L156),0) [....] Doing an 'Evaluate Formula' gives IF(TRUE,IF(TRUE,(58511.55+86.66-F157),L156),0) and then IF(TRUE,IF(TRUE,(58,598.2100000001-F157),L156),0) The following duplicates what you observed. But note: this is not the only way it can happen. Suppose L156 contains a formula that results in 58511.55 + 6*2^-37 -- just a little larger than 58511.55, but not large enough for the difference to appear in the first 15 significant digits. Suppose G157 contains the constant 86.66, and F157 is empty or zero as you indicated. Then if the cell containing the IF() formula or a reference to it is formatted as Number with 10 decimal places, Evaluate Formula will behave as you describe. The reason is: 1. 58511.55 + 6*2^-37 is exactly 58511.5500000000,4656612873077392578125 internally, which displays 58511.5500000000. 2. 86.66 is exactly 86.6599999999999,96589394868351519107818603515625 internally, which displays as 86.6600000000000. 3. Their sum is exactly 58598.2100000000,5005858838558197021484375 internally, which displays as 58598.2100000001 because the 16th significant digit is 5 or more, roughly speaking. Although 58511.55 + 6*2^-37 is contrived here, this kind of small numerical abberation can and does arise normally as the result of arithmetic calculations and Excel functions. This example demonstrates that formatting to show 15 significant digits does not always reveal what it is happening internally. Changing the 86.66 to .65 or .67 removes the 1 at the 10th decimal place. Because the exact internal values of those sums are 58598.2000000000,480213202536106109619140625 and 58598.2200000000,44819898903369903564453125 respectively. The 15th significant digit is not rounded up because the 16th significant digit is less than 5, roughly speaking. FYI, the exact internal values of 86.65 and 86.67 are 86.6500000000000,05684341886080801486968994140625 and 86.6700000000000,017053025658242404460906982421875 respectively. PS: Again, the comma in the presentation of the exact internal values is my way of demarcating the first 15 significant digits to the left. ----- original message ----- "JoeU2004" wrote in message ... "Mister_T" wrote: AAARRRRGGGHHHHH!!!!! What is going on. I don't see how this can be anything other than a bug. I have searched the MS site to look for a way of reporting a bug but there doesn't seem to be one. Anyone got any ideas on this probelm or know how to report a bug No, this is not a defect. It is a side-effect of the way that Excel (and most applications) do arithmetic on binary computers. But before I get into my explanation, here some pointers to Microsoft's: http://support.microsoft.com/kb/78113 http://support.microsoft.com/kb/42980 http://support.microsoft.com/kb/214118 (58,511.55 + 86.66 - <empty cell) [The 58k value is itself a result of another formula like the above] You would expect 58,598.21 but instead excel gives 58,598.210000000100000 Your parenthetical comment (58511.55 is the result of a formula) is the key. In this particular case, we cannot duplicate your results simply by typing (58511.55 + 86.66 - 0), with or without the parentheses, which surprisingly can make a difference sometimes. In your case, the expression does not equal 58598.21 "exactly" (i.e. within 15 significant digits) because 58.511.55 and/or 86.66 are not "exactly" those values within 15 significant digits. Format each cell as Scientific with 14 decimal places, and you should see the disparity. (If you are unfamiliar with Scientific notation, don't worry. It is just a consistent way of seeing 15 significant digits regardless of the magnitude of the number.) What you probably really want to know is: how do you avoid this very common anomaly? The answer is: use ROUND judicious and, IMHO, prolifically. For example, ROUND(A1+A2-A3,2), where A1 has the formula that results in 58511.55, A2 has the formula that results in 86.66, and A3 is the empty cell. (Note: Another alternative is to set the "Precision as displayed" option under Tools Options Calcuation. I do not recommend it for some very specific reasons. If you are interested, post a response in this thread, and I will explain.) Since I cannot duplicate your example, lacking the exact values, I'll use my favorite examples to explain. Consider 10.1 - 10 in A1 and 0.1 in A2. =(A1=A2) is FALSE, and =A1-A2 is -3.608E-16. In a nutshell, this is because numbers are represented internally by 53 consecutive powers of 2 ("bits"). With 0.1, we can use the full 53 bits to represent 0.1. But with 10.1, some of the bits are used to represent 10; so there are fewer bits to represent 0.1. In this case, that results in a different representation of 0.1 as part of 10.1. When we subtract 10, we are left with this different representation. This might be clearer if you could see the exact decimal representation of the internal values. Excel will not do that; it limits itself to presenting only the first 15 significant digits. But the exact internal value of 10.1 is 10.0999999999999,996447286321199499070644378662109 375; the exact internal value of 10.1 - 0.1 is 0.0999999999999996,4472863211994990706443786621093 75; and the exact internal value of 0.1 is 0.100000000000000,00555111512312578270211815834045 41015625. (The comma is my way of demarcating the first 15 significant digits to the left.) The exact internal values of 10.1 - 0.1 and 0.1 are not close enough for Excel to consider them equal. Note the words "close enough". Excel has implemented some heuristics to consider two different internal values as equal under some very narrow conditions. Unfortunately, those heuristics often add to the confusion, in part because they are poorly defined, IMHO. For example, consider 0.1 + 2^-56 in A1 (just slightly larger than 0.1) and 0.1 in A2. =(A1=A2) returns TRUE even though the internal values obviously are not equal. Likewise, =A1-A2 returns zero. But =(A1-A2) returns non-zero, namely about 1.39E-17; note that the only difference is the parentheses. And for the same reason, =IF(A1-A2=0,TRUE) returns FALSE, which flies in the face of reason considering the result of =A1-A2. The latter example is explained somewhat if you read between the lines under the heading "Example When a Value Reaches Zero" on the web page at http://support.microsoft.com/kb/78113 . The explanation is flawed(!); but it is the best that Microsoft has to offer. You would not like my more-precise explanation, which I reverse-engineered. It is really too techy. (But if you really want to know, ask for it in a response in this thread.) Hope this helps. I would be happy to go into more detail if you have questions. It might be helpful if you posted the Scientific format (with 14 dp) of the numbers in question. But bear in mind that even that is sometimes not good enough for us to see the difference; consider my example of 0.1 + 2^-56. At the very least, I hope the "short" explanation above demonstrates that this is not a defect per se. ----- original message ----- "Mister_T" wrote in message ... I have the following formula in excel: =IF((F157+G157)0,IF(B157<"#",(L156+G157-F157),L156),0) The first two IFs evaluate to true so the result is basicaly L156+G157-F157 and the values are (58,511.55 + 86.66 - <empty cell) [The 58k value is itself a result of another formula like the above] You would expect 58,598.21 but instead excel gives 58,598.210000000100000 Setting up three additional formulas that are just an equals on the two data cells and the result gives the following: 58,511.55000000000000000 86.6600000000000000000 58,598.210000000100000000 Doing an 'Evaluate Formula' gives IF(TRUE,IF(TRUE,(58511.55+86.66-F157),L156),0) and then IF(TRUE,IF(TRUE,(58,598.2100000001-F157),L156),0) AAARRRRGGGHHHHH!!!!! What is going on. I don't see how this can be anything other than a bug. I have searched the MS site to look for a way of reporting a bug but there doesn't seem to be one. Anyone got any ideas on this probelm or know how to report a bug Changing the 86.66 to .65 or .67 removes the 1 at the 10th decimal place. I can work around it by using two rows in my sheet to make up the 86.66 transaction but if this keeps happening it is going to be very annoying. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this a bug in excel?
PPS....
I wrote: "Mister_T" wrote: =IF((F157+G157)0,IF(B157<"#",(L156+G157-F157),L156),0) [....] 1. 58511.55 + 6*2^-37 is exactly 58511.5500000000,4656612873077392578125 internally, which displays 58511.5500000000. As I indicated previously, prolific use of ROUND() can ameliorate this kind of problem. (But sometimes it creates new ones. Caveat emptor.) For example, I would round the formula in L156. Using my example: =ROUND(58511.55 + 6*2^-37, 2). And I would round arithemetic in the IF() formula, namely: =IF(ROUND(F157+G157,2)0, IF(B157<"#", ROUND((L156+G157-F157,2), L156), 0) Whether or not ROUND is appropriate needs to be determined on a case-by-case basis, based on your intent. ----- original message ----- "JoeU2004" wrote in message ... PS.... "Mister_T" wrote: =IF((F157+G157)0,IF(B157<"#",(L156+G157-F157),L156),0) [....] Doing an 'Evaluate Formula' gives IF(TRUE,IF(TRUE,(58511.55+86.66-F157),L156),0) and then IF(TRUE,IF(TRUE,(58,598.2100000001-F157),L156),0) The following duplicates what you observed. But note: this is not the only way it can happen. Suppose L156 contains a formula that results in 58511.55 + 6*2^-37 -- just a little larger than 58511.55, but not large enough for the difference to appear in the first 15 significant digits. Suppose G157 contains the constant 86.66, and F157 is empty or zero as you indicated. Then if the cell containing the IF() formula or a reference to it is formatted as Number with 10 decimal places, Evaluate Formula will behave as you describe. The reason is: 1. 58511.55 + 6*2^-37 is exactly 58511.5500000000,4656612873077392578125 internally, which displays 58511.5500000000. 2. 86.66 is exactly 86.6599999999999,96589394868351519107818603515625 internally, which displays as 86.6600000000000. 3. Their sum is exactly 58598.2100000000,5005858838558197021484375 internally, which displays as 58598.2100000001 because the 16th significant digit is 5 or more, roughly speaking. Although 58511.55 + 6*2^-37 is contrived here, this kind of small numerical abberation can and does arise normally as the result of arithmetic calculations and Excel functions. This example demonstrates that formatting to show 15 significant digits does not always reveal what it is happening internally. Changing the 86.66 to .65 or .67 removes the 1 at the 10th decimal place. Because the exact internal values of those sums are 58598.2000000000,480213202536106109619140625 and 58598.2200000000,44819898903369903564453125 respectively. The 15th significant digit is not rounded up because the 16th significant digit is less than 5, roughly speaking. FYI, the exact internal values of 86.65 and 86.67 are 86.6500000000000,05684341886080801486968994140625 and 86.6700000000000,017053025658242404460906982421875 respectively. PS: Again, the comma in the presentation of the exact internal values is my way of demarcating the first 15 significant digits to the left. ----- original message ----- "JoeU2004" wrote in message ... "Mister_T" wrote: AAARRRRGGGHHHHH!!!!! What is going on. I don't see how this can be anything other than a bug. I have searched the MS site to look for a way of reporting a bug but there doesn't seem to be one. Anyone got any ideas on this probelm or know how to report a bug No, this is not a defect. It is a side-effect of the way that Excel (and most applications) do arithmetic on binary computers. But before I get into my explanation, here some pointers to Microsoft's: http://support.microsoft.com/kb/78113 http://support.microsoft.com/kb/42980 http://support.microsoft.com/kb/214118 (58,511.55 + 86.66 - <empty cell) [The 58k value is itself a result of another formula like the above] You would expect 58,598.21 but instead excel gives 58,598.210000000100000 Your parenthetical comment (58511.55 is the result of a formula) is the key. In this particular case, we cannot duplicate your results simply by typing (58511.55 + 86.66 - 0), with or without the parentheses, which surprisingly can make a difference sometimes. In your case, the expression does not equal 58598.21 "exactly" (i.e. within 15 significant digits) because 58.511.55 and/or 86.66 are not "exactly" those values within 15 significant digits. Format each cell as Scientific with 14 decimal places, and you should see the disparity. (If you are unfamiliar with Scientific notation, don't worry. It is just a consistent way of seeing 15 significant digits regardless of the magnitude of the number.) What you probably really want to know is: how do you avoid this very common anomaly? The answer is: use ROUND judicious and, IMHO, prolifically. For example, ROUND(A1+A2-A3,2), where A1 has the formula that results in 58511.55, A2 has the formula that results in 86.66, and A3 is the empty cell. (Note: Another alternative is to set the "Precision as displayed" option under Tools Options Calcuation. I do not recommend it for some very specific reasons. If you are interested, post a response in this thread, and I will explain.) Since I cannot duplicate your example, lacking the exact values, I'll use my favorite examples to explain. Consider 10.1 - 10 in A1 and 0.1 in A2. =(A1=A2) is FALSE, and =A1-A2 is -3.608E-16. In a nutshell, this is because numbers are represented internally by 53 consecutive powers of 2 ("bits"). With 0.1, we can use the full 53 bits to represent 0.1. But with 10.1, some of the bits are used to represent 10; so there are fewer bits to represent 0.1. In this case, that results in a different representation of 0.1 as part of 10.1. When we subtract 10, we are left with this different representation. This might be clearer if you could see the exact decimal representation of the internal values. Excel will not do that; it limits itself to presenting only the first 15 significant digits. But the exact internal value of 10.1 is 10.0999999999999,996447286321199499070644378662109 375; the exact internal value of 10.1 - 0.1 is 0.0999999999999996,4472863211994990706443786621093 75; and the exact internal value of 0.1 is 0.100000000000000,00555111512312578270211815834045 41015625. (The comma is my way of demarcating the first 15 significant digits to the left.) The exact internal values of 10.1 - 0.1 and 0.1 are not close enough for Excel to consider them equal. Note the words "close enough". Excel has implemented some heuristics to consider two different internal values as equal under some very narrow conditions. Unfortunately, those heuristics often add to the confusion, in part because they are poorly defined, IMHO. For example, consider 0.1 + 2^-56 in A1 (just slightly larger than 0.1) and 0.1 in A2. =(A1=A2) returns TRUE even though the internal values obviously are not equal. Likewise, =A1-A2 returns zero. But =(A1-A2) returns non-zero, namely about 1.39E-17; note that the only difference is the parentheses. And for the same reason, =IF(A1-A2=0,TRUE) returns FALSE, which flies in the face of reason considering the result of =A1-A2. The latter example is explained somewhat if you read between the lines under the heading "Example When a Value Reaches Zero" on the web page at http://support.microsoft.com/kb/78113 . The explanation is flawed(!); but it is the best that Microsoft has to offer. You would not like my more-precise explanation, which I reverse-engineered. It is really too techy. (But if you really want to know, ask for it in a response in this thread.) Hope this helps. I would be happy to go into more detail if you have questions. It might be helpful if you posted the Scientific format (with 14 dp) of the numbers in question. But bear in mind that even that is sometimes not good enough for us to see the difference; consider my example of 0.1 + 2^-56. At the very least, I hope the "short" explanation above demonstrates that this is not a defect per se. ----- original message ----- "Mister_T" wrote in message ... I have the following formula in excel: =IF((F157+G157)0,IF(B157<"#",(L156+G157-F157),L156),0) The first two IFs evaluate to true so the result is basicaly L156+G157-F157 and the values are (58,511.55 + 86.66 - <empty cell) [The 58k value is itself a result of another formula like the above] You would expect 58,598.21 but instead excel gives 58,598.210000000100000 Setting up three additional formulas that are just an equals on the two data cells and the result gives the following: 58,511.55000000000000000 86.6600000000000000000 58,598.210000000100000000 Doing an 'Evaluate Formula' gives IF(TRUE,IF(TRUE,(58511.55+86.66-F157),L156),0) and then IF(TRUE,IF(TRUE,(58,598.2100000001-F157),L156),0) AAARRRRGGGHHHHH!!!!! What is going on. I don't see how this can be anything other than a bug. I have searched the MS site to look for a way of reporting a bug but there doesn't seem to be one. Anyone got any ideas on this probelm or know how to report a bug Changing the 86.66 to .65 or .67 removes the 1 at the 10th decimal place. I can work around it by using two rows in my sheet to make up the 86.66 transaction but if this keeps happening it is going to be very annoying. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is this a bug in excel?
"Bernard Liengme" wrote...
Sound like Harlan once programmed in COBOL where decimal math is the default cheers .... Ugh! Not COBOL, PL/1. Binary coded decimal RULEZ! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|