|
Excel Bug - Excel Geeks Unite!!
Ok here is whats going on,
227.82 227 0.75 0.05 0.02 227.82 False inputed 227.82 then sum 227+.75+.05+.02=227.82 then the last cell is an IF THEN statement If cell1 - cell5 = 0 then display True (ie 227.82-227.82=0 then "True") Tell me why excel insists that this statement is false?? |
Nature of binary arithmetic. 227.82 doesn't REALLY com out that way --
something like 227.81999999999994 or so. When you evaluate the formula (using tools/formula auditing/evaluate formula), you'll see that the simple subtraction comes out 2.8421709430404E-14, which isn't zero. Change your formula to something like =IF(ROUND(Cell1-cell5,5)=0,TRUE,FALSE) Bob Umlas Excel MVP "Joey Bag O" <Joey Bag wrote in message ... Ok here is whats going on, 227.82 227 0.75 0.05 0.02 227.82 False inputed 227.82 then sum 227+.75+.05+.02=227.82 then the last cell is an IF THEN statement If cell1 - cell5 = 0 then display True (ie 227.82-227.82=0 then "True") Tell me why excel insists that this statement is false?? |
Most decimal fractions (including .82, .05, and .02) have no exact
finite binary representation, just as 1/3 has no exact decimal representation. Since your inputs must be approximated, the results are only approximate. Programming around this issue in floating point calculations has been standard for over half a century (long before Excel was a gleam in Bill's eye). To understand the problem intuitively, consider a hypothetical computer that does decimal arithmetic with 4 significant figures. 1 = 1/3 + 1/3 + 1/3 but on this hypothetical computer, then 1/3 = 0.3333 so 1/3 + 1/3 +1/3 = 0.9999 < 1 Excel (and almost all other general purpose software) uses IEEE double precision binary arithmetic. The IEEE double precision approximation for 227.82 is 227.81999999999999317878973670303821563720703125 the approximation for 0.05 is 0.050000000000000002775557561562891351059079170227 05078125 the approximation for 0.02 is 0.020000000000000000416333634234433702658861875534 0576171875 Hence in binary 227+0.75+0.05+0.02 is greater than the binary approximation to 227.82. That is why =(227.82-(227+0.75+0.05+0.02)) returns -2.8421709430404E-14 (the correct result of the binary operations) instead of zero. You will get similar results in almost all general purpose software, unless they apply some sort of fuzz factor to the calculations. Excel applies a fuzz factor if the subtraction is the last operation, so that =227.82-(227+0.75+0.05+0.02) will return zero, but this fuzz factor does not apply inside an IF() function. Give the nature of the issue, two simple and theoretically correct way to do your IF would be =IF( ROUND(cell1-cell5,2)=0, TRUE, FALSE) =IF( ABS(cell1-cell5)<epsilon, TRUE, FALSE) where epsilon is a suitably small number (<0.01 in this case). Jerry Joey Bag O wrote: Ok here is whats going on, 227.82 227 0.75 0.05 0.02 227.82 False inputed 227.82 then sum 227+.75+.05+.02=227.82 then the last cell is an IF THEN statement If cell1 - cell5 = 0 then display True (ie 227.82-227.82=0 then "True") Tell me why excel insists that this statement is false?? |
It would have been nice if Excel had afforded the user an opportunity to
round "globally" all cells to a specified precision, say 13 significant figures (user-definable). That way if you are confident that no calculation in a workbook should create a 14+ significant figure other than zero it would automatically strip out the errors arising from the binary conversion. Of course you can do this by manually inserting an =ROUND(ref,13) function around every cell that contains a formula (OK, that would round to decimal places rather than significant figures), but what a palava when with a bit of design work a single check-box could do it. The effect would be different from the kludgy "precision as displayed" option currently available. I remember when I first came across this effect many years ago I was surprised that when I imported the offending file into SuperCalc it seemed to strip out some of the rounding errors automatically that Excel generated. Not sure how SuperCalc managed that. "Jerry W. Lewis" wrote in message ... Most decimal fractions (including .82, .05, and .02) have no exact finite binary representation, just as 1/3 has no exact decimal representation. Since your inputs must be approximated, the results are only approximate. Programming around this issue in floating point calculations has been standard for over half a century (long before Excel was a gleam in Bill's eye). To understand the problem intuitively, consider a hypothetical computer that does decimal arithmetic with 4 significant figures. 1 = 1/3 + 1/3 + 1/3 but on this hypothetical computer, then 1/3 = 0.3333 so 1/3 + 1/3 +1/3 = 0.9999 < 1 Excel (and almost all other general purpose software) uses IEEE double precision binary arithmetic. The IEEE double precision approximation for 227.82 is 227.81999999999999317878973670303821563720703125 the approximation for 0.05 is 0.050000000000000002775557561562891351059079170227 05078125 the approximation for 0.02 is 0.020000000000000000416333634234433702658861875534 0576171875 Hence in binary 227+0.75+0.05+0.02 is greater than the binary approximation to 227.82. That is why =(227.82-(227+0.75+0.05+0.02)) returns -2.8421709430404E-14 (the correct result of the binary operations) instead of zero. You will get similar results in almost all general purpose software, unless they apply some sort of fuzz factor to the calculations. Excel applies a fuzz factor if the subtraction is the last operation, so that =227.82-(227+0.75+0.05+0.02) will return zero, but this fuzz factor does not apply inside an IF() function. Give the nature of the issue, two simple and theoretically correct way to do your IF would be =IF( ROUND(cell1-cell5,2)=0, TRUE, FALSE) =IF( ABS(cell1-cell5)<epsilon, TRUE, FALSE) where epsilon is a suitably small number (<0.01 in this case). Jerry Joey Bag O wrote: Ok here is whats going on, 227.82 227 0.75 0.05 0.02 227.82 False inputed 227.82 then sum 227+.75+.05+.02=227.82 then the last cell is an IF THEN statement If cell1 - cell5 = 0 then display True (ie 227.82-227.82=0 then "True") Tell me why excel insists that this statement is false?? |
Tools|Options|Calculation|Precision as displayed
Will round all values to cell format precision. Rounding to a specified number of significant figures does not necessarily solve the problem (see my previous decimal example involving 1/3. The last time I saw SuperCalc, many packages were using custom floating point number formats, which would allow the possibility of carrying many guard digits to reduce the likelihood of seeing these issues (I don't know if that is in fact what SuperCalc did). That situation was potentially frustrating to serious numerical analysts, since it meant that there was far more precision available than you were allowed to use. These days, almost everyone (including Excel) follows the IEEE standard. The few exceptions use binary coded decimal (BCD) or adjustable precision for special purpose applications. Given the nature of this issue, I would tend to distrust any current package that does not exhibit the normal effects of finite precision binary approximation, unless the reason is clearly documented. To do hide these issues while doing binary math, the package would have to make assumptions that may not be valid. I generally prefer that software not try to think for me. Jerry Jack Sheet wrote: It would have been nice if Excel had afforded the user an opportunity to round "globally" all cells to a specified precision, say 13 significant figures (user-definable). That way if you are confident that no calculation in a workbook should create a 14+ significant figure other than zero it would automatically strip out the errors arising from the binary conversion. Of course you can do this by manually inserting an =ROUND(ref,13) function around every cell that contains a formula (OK, that would round to decimal places rather than significant figures), but what a palava when with a bit of design work a single check-box could do it. The effect would be different from the kludgy "precision as displayed" option currently available. I remember when I first came across this effect many years ago I was surprised that when I imported the offending file into SuperCalc it seemed to strip out some of the rounding errors automatically that Excel generated. Not sure how SuperCalc managed that. "Jerry W. Lewis" wrote in message ... Most decimal fractions (including .82, .05, and .02) have no exact finite binary representation, just as 1/3 has no exact decimal representation. Since your inputs must be approximated, the results are only approximate. Programming around this issue in floating point calculations has been standard for over half a century (long before Excel was a gleam in Bill's eye). To understand the problem intuitively, consider a hypothetical computer that does decimal arithmetic with 4 significant figures. 1 = 1/3 + 1/3 + 1/3 but on this hypothetical computer, then 1/3 = 0.3333 so 1/3 + 1/3 +1/3 = 0.9999 < 1 Excel (and almost all other general purpose software) uses IEEE double precision binary arithmetic. The IEEE double precision approximation for 227.82 is 227.81999999999999317878973670303821563720703125 the approximation for 0.05 is 0.050000000000000002775557561562891351059079170227 05078125 the approximation for 0.02 is 0.020000000000000000416333634234433702658861875534 0576171875 Hence in binary 227+0.75+0.05+0.02 is greater than the binary approximation to 227.82. That is why =(227.82-(227+0.75+0.05+0.02)) returns -2.8421709430404E-14 (the correct result of the binary operations) instead of zero. You will get similar results in almost all general purpose software, unless they apply some sort of fuzz factor to the calculations. Excel applies a fuzz factor if the subtraction is the last operation, so that =227.82-(227+0.75+0.05+0.02) will return zero, but this fuzz factor does not apply inside an IF() function. Give the nature of the issue, two simple and theoretically correct way to do your IF would be =IF( ROUND(cell1-cell5,2)=0, TRUE, FALSE) =IF( ABS(cell1-cell5)<epsilon, TRUE, FALSE) where epsilon is a suitably small number (<0.01 in this case). Jerry Joey Bag O wrote: Ok here is whats going on, 227.82 227 0.75 0.05 0.02 227.82 False inputed 227.82 then sum 227+.75+.05+.02=227.82 then the last cell is an IF THEN statement If cell1 - cell5 = 0 then display True (ie 227.82-227.82=0 then "True") Tell me why excel insists that this statement is false?? |
Points well taken. Although under my suggestion you would still have the
option to untick the checkbox that causes the global rounding, and thereby revert to IEEE standard. "Jerry W. Lewis" wrote in message ... Tools|Options|Calculation|Precision as displayed Will round all values to cell format precision. Rounding to a specified number of significant figures does not necessarily solve the problem (see my previous decimal example involving 1/3. The last time I saw SuperCalc, many packages were using custom floating point number formats, which would allow the possibility of carrying many guard digits to reduce the likelihood of seeing these issues (I don't know if that is in fact what SuperCalc did). That situation was potentially frustrating to serious numerical analysts, since it meant that there was far more precision available than you were allowed to use. These days, almost everyone (including Excel) follows the IEEE standard. The few exceptions use binary coded decimal (BCD) or adjustable precision for special purpose applications. Given the nature of this issue, I would tend to distrust any current package that does not exhibit the normal effects of finite precision binary approximation, unless the reason is clearly documented. To do hide these issues while doing binary math, the package would have to make assumptions that may not be valid. I generally prefer that software not try to think for me. Jerry Jack Sheet wrote: It would have been nice if Excel had afforded the user an opportunity to round "globally" all cells to a specified precision, say 13 significant figures (user-definable). That way if you are confident that no calculation in a workbook should create a 14+ significant figure other than zero it would automatically strip out the errors arising from the binary conversion. Of course you can do this by manually inserting an =ROUND(ref,13) function around every cell that contains a formula (OK, that would round to decimal places rather than significant figures), but what a palava when with a bit of design work a single check-box could do it. The effect would be different from the kludgy "precision as displayed" option currently available. I remember when I first came across this effect many years ago I was surprised that when I imported the offending file into SuperCalc it seemed to strip out some of the rounding errors automatically that Excel generated. Not sure how SuperCalc managed that. "Jerry W. Lewis" wrote in message ... Most decimal fractions (including .82, .05, and .02) have no exact finite binary representation, just as 1/3 has no exact decimal representation. Since your inputs must be approximated, the results are only approximate. Programming around this issue in floating point calculations has been standard for over half a century (long before Excel was a gleam in Bill's eye). To understand the problem intuitively, consider a hypothetical computer that does decimal arithmetic with 4 significant figures. 1 = 1/3 + 1/3 + 1/3 but on this hypothetical computer, then 1/3 = 0.3333 so 1/3 + 1/3 +1/3 = 0.9999 < 1 Excel (and almost all other general purpose software) uses IEEE double precision binary arithmetic. The IEEE double precision approximation for 227.82 is 227.81999999999999317878973670303821563720703125 the approximation for 0.05 is 0.050000000000000002775557561562891351059079170227 05078125 the approximation for 0.02 is 0.020000000000000000416333634234433702658861875534 0576171875 Hence in binary 227+0.75+0.05+0.02 is greater than the binary approximation to 227.82. That is why =(227.82-(227+0.75+0.05+0.02)) returns -2.8421709430404E-14 (the correct result of the binary operations) instead of zero. You will get similar results in almost all general purpose software, unless they apply some sort of fuzz factor to the calculations. Excel applies a fuzz factor if the subtraction is the last operation, so that =227.82-(227+0.75+0.05+0.02) will return zero, but this fuzz factor does not apply inside an IF() function. Give the nature of the issue, two simple and theoretically correct way to do your IF would be =IF( ROUND(cell1-cell5,2)=0, TRUE, FALSE) =IF( ABS(cell1-cell5)<epsilon, TRUE, FALSE) where epsilon is a suitably small number (<0.01 in this case). Jerry Joey Bag O wrote: Ok here is whats going on, 227.82 227 0.75 0.05 0.02 227.82 False inputed 227.82 then sum 227+.75+.05+.02=227.82 then the last cell is an IF THEN statement If cell1 - cell5 = 0 then display True (ie 227.82-227.82=0 then "True") Tell me why excel insists that this statement is false?? |
| All times are GMT +1. The time now is 02:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com