Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Accuracy
Using Excel 2003 I "discover" a strange thing. Placing brackets around a term
introduces an inaccuracy. Is this normal? 1-(1/cos(x)*cos(a.t+x) is nil for t=0 as it should be. But I use this term in a series, so I have to place brackets around the term: (1-(1/cos(x)*cos(a.t+x)) And surprisingly for t=0 I get the answer 1.1 E(-14). It's small but I expect at least a value below E(-22) or 0 Reactions are welcomed Ad Pronk |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Accuracy
Look he
http://support.microsoft.com/kb/78113 -- Kind regards, Niek Otten Microsoft MVP - Excel "Ad Pronk" wrote in message ... Using Excel 2003 I "discover" a strange thing. Placing brackets around a term introduces an inaccuracy. Is this normal? 1-(1/cos(x)*cos(a.t+x) is nil for t=0 as it should be. But I use this term in a series, so I have to place brackets around the term: (1-(1/cos(x)*cos(a.t+x)) And surprisingly for t=0 I get the answer 1.1 E(-14). It's small but I expect at least a value below E(-22) or 0 Reactions are welcomed Ad Pronk |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Accuracy
That is not an XL issue but rather it is a computer issue in general. Decimal
numbers will only have a certain degree of accuracy based on processing and memory limitations of PC's. Google IEEE and floating point for an in depth discussion of the issue. In general you will get 15 significant digits of accuracy. The numbers after that or of dubious quality. That being said with 15 digit of accuracy you can measure the distance from here to the sun to less than a millimeter. -- HTH... Jim Thomlinson "Ad Pronk" wrote: Using Excel 2003 I "discover" a strange thing. Placing brackets around a term introduces an inaccuracy. Is this normal? 1-(1/cos(x)*cos(a.t+x) is nil for t=0 as it should be. But I use this term in a series, so I have to place brackets around the term: (1-(1/cos(x)*cos(a.t+x)) And surprisingly for t=0 I get the answer 1.1 E(-14). It's small but I expect at least a value below E(-22) or 0 Reactions are welcomed Ad Pronk |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Accuracy
"Ad Pronk" wrote:
Using Excel 2003 I "discover" a strange thing. Placing brackets around a term introduces an inaccuracy. Is this normal? Yes, to some degree. It is questionable whether the parentheses introduce an "inaccuracy" per se. But they certainly can change the result. Since you neglected to include details (klunk!), I cannot reproduce and discuss exactly what you are seeing. But generally, adding parentheses to expressions can change the order of evaluation; the same is true by simply reordering terms. And that can change the final outcome in some cases, depending on the numbers involved. Here is a simple example: A1: =2226-123.36-39.34 A2: =2226-(123.36+39.34) A3: =-123.36-39.34+2226 All results are displayed as 2063.3 in General format. In fact, they even appear identical if you format as Scientific with 14 decimal places, the most number of significant digits (15) that Excel will format. Moreover, =A1=A2 returns TRUE, and =A1-A2 returns exactly zero. But =A1-A2=0 returns FALSE, and =(A1-A2) returns about -4.547E-13. Note that the latter is not an "inaccuracy". In fact, it is the more accurate difference between the values in A1 and A2. Not surprisingly, A3 has exactly the same result as A2, even without additional parentheses. The primary issue is the way that Excel (and most applications) store numbers and perform arithmetic by default. It is called binary floating point -- more specifically, IEEE-754 double-precision floating point. A secondary issue is various Excel heuristics (algorithms), which are intended to hide some of the anomalies arise with floating-point arithmetic. Although well-intentioned, the heuristics are half-baked, often creating even stranger results. This is the reason why A1=A2 is return TRUE, but A1-A2=0 is FALSE above. The work-around is to use ROUND prolifically, but prudently. That is, explicitly round results of most arithmetic expressions that might result in fractional digits. Another common alternative is to use expressions of the form ABS(A1-A2)<0.01 when comparing such numbers. But that has obvious limited application, and it can result in surprises because 0.01 cannot be represented exactly in binary floating point. Alternatively, you might consider using the "Precision as displayed" calculation option. I deprecate the use of that feature because of its uncontrolled pervasive effect. If you choose to experiment with it, you should first make a copy of the Excel workbook file. Some changes cause by setting PAD are irreversible. ----- original message ----- "Ad Pronk" wrote in message ... Using Excel 2003 I "discover" a strange thing. Placing brackets around a term introduces an inaccuracy. Is this normal? 1-(1/cos(x)*cos(a.t+x) is nil for t=0 as it should be. But I use this term in a series, so I have to place brackets around the term: (1-(1/cos(x)*cos(a.t+x)) And surprisingly for t=0 I get the answer 1.1 E(-14). It's small but I expect at least a value below E(-22) or 0 Reactions are welcomed Ad Pronk |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Accuracy
Sorry, but none of that seems to explain his issue
2+2 shoudl always be the same as (2+2), regardless of what is actully represented by 2+2. Yes, Parentheses change the order of operations, but only between what is inside and what is outside of those parenthesis. Everything WITHIN the parenthesis should follow it's own OoO, and wrapping a set around an entire formula should NEVER change the results. 1-(1/cos(x)*cos(a.t+x) is mathmaticly equivilent to (1-(1/cos(x)*cos(a.t+x)) neither decimal/binary conversions issues, or storage issues, or anything should change this. Otherwise, it is a bug. I remember the days of the old ATARI 400/800 computers. They used BCD, (binary coded decimal) and to the limits of thier implentation, they were dead on accurate. Why, with our far more powerful computers and a need for precision, BCD is not at least on optoin, (scalable to any need, potentially accurate to pretty much any precision,) is beyond me. Phil Joe User wrote: "Ad Pronk" wrote: Using Excel 2003 I "discover" a strange thing. Placing brackets around a term introduces an inaccuracy. Is this normal? Yes, to some degree. It is questionable whether the parentheses introduce an "inaccuracy" per se. But they certainly can change the result. Since you neglected to include details (klunk!), I cannot reproduce and discuss exactly what you are seeing. But generally, adding parentheses to expressions can change the order of evaluation; the same is true by simply reordering terms. And that can change the final outcome in some cases, depending on the numbers involved. Here is a simple example: A1: =2226-123.36-39.34 A2: =2226-(123.36+39.34) A3: =-123.36-39.34+2226 All results are displayed as 2063.3 in General format. In fact, they even appear identical if you format as Scientific with 14 decimal places, the most number of significant digits (15) that Excel will format. Moreover, =A1=A2 returns TRUE, and =A1-A2 returns exactly zero. But =A1-A2=0 returns FALSE, and =(A1-A2) returns about -4.547E-13. Note that the latter is not an "inaccuracy". In fact, it is the more accurate difference between the values in A1 and A2. Not surprisingly, A3 has exactly the same result as A2, even without additional parentheses. The primary issue is the way that Excel (and most applications) store numbers and perform arithmetic by default. It is called binary floating point -- more specifically, IEEE-754 double-precision floating point. A secondary issue is various Excel heuristics (algorithms), which are intended to hide some of the anomalies arise with floating-point arithmetic. Although well-intentioned, the heuristics are half-baked, often creating even stranger results. This is the reason why A1=A2 is return TRUE, but A1-A2=0 is FALSE above. The work-around is to use ROUND prolifically, but prudently. That is, explicitly round results of most arithmetic expressions that might result in fractional digits. Another common alternative is to use expressions of the form ABS(A1-A2)<0.01 when comparing such numbers. But that has obvious limited application, and it can result in surprises because 0.01 cannot be represented exactly in binary floating point. Alternatively, you might consider using the "Precision as displayed" calculation option. I deprecate the use of that feature because of its uncontrolled pervasive effect. If you choose to experiment with it, you should first make a copy of the Excel workbook file. Some changes cause by setting PAD are irreversible. ----- original message ----- "Ad Pronk" wrote in message ... Using Excel 2003 I "discover" a strange thing. Placing brackets around a term introduces an inaccuracy. Is this normal? 1-(1/cos(x)*cos(a.t+x) is nil for t=0 as it should be. But I use this term in a series, so I have to place brackets around the term: (1-(1/cos(x)*cos(a.t+x)) And surprisingly for t=0 I get the answer 1.1 E(-14). It's small but I expect at least a value below E(-22) or 0 Reactions are welcomed Ad Pronk |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Accuracy
1-(1/cos(x)*cos(a.t+x) doesn't even have its parentheses in matching pairs,
and nor does (1-(1/cos(x)*cos(a.t+x)). It might have been useful if the OP had shown us what formulae he was actually using. -- David Biddulph "Phil Smith" wrote in message ... Sorry, but none of that seems to explain his issue 2+2 shoudl always be the same as (2+2), regardless of what is actully represented by 2+2. Yes, Parentheses change the order of operations, but only between what is inside and what is outside of those parenthesis. Everything WITHIN the parenthesis should follow it's own OoO, and wrapping a set around an entire formula should NEVER change the results. 1-(1/cos(x)*cos(a.t+x) is mathmaticly equivilent to (1-(1/cos(x)*cos(a.t+x)) neither decimal/binary conversions issues, or storage issues, or anything should change this. Otherwise, it is a bug. I remember the days of the old ATARI 400/800 computers. They used BCD, (binary coded decimal) and to the limits of thier implentation, they were dead on accurate. Why, with our far more powerful computers and a need for precision, BCD is not at least on optoin, (scalable to any need, potentially accurate to pretty much any precision,) is beyond me. Phil Joe User wrote: "Ad Pronk" wrote: Using Excel 2003 I "discover" a strange thing. Placing brackets around a term introduces an inaccuracy. Is this normal? Yes, to some degree. It is questionable whether the parentheses introduce an "inaccuracy" per se. But they certainly can change the result. Since you neglected to include details (klunk!), I cannot reproduce and discuss exactly what you are seeing. But generally, adding parentheses to expressions can change the order of evaluation; the same is true by simply reordering terms. And that can change the final outcome in some cases, depending on the numbers involved. Here is a simple example: A1: =2226-123.36-39.34 A2: =2226-(123.36+39.34) A3: =-123.36-39.34+2226 All results are displayed as 2063.3 in General format. In fact, they even appear identical if you format as Scientific with 14 decimal places, the most number of significant digits (15) that Excel will format. Moreover, =A1=A2 returns TRUE, and =A1-A2 returns exactly zero. But =A1-A2=0 returns FALSE, and =(A1-A2) returns about -4.547E-13. Note that the latter is not an "inaccuracy". In fact, it is the more accurate difference between the values in A1 and A2. Not surprisingly, A3 has exactly the same result as A2, even without additional parentheses. The primary issue is the way that Excel (and most applications) store numbers and perform arithmetic by default. It is called binary floating point -- more specifically, IEEE-754 double-precision floating point. A secondary issue is various Excel heuristics (algorithms), which are intended to hide some of the anomalies arise with floating-point arithmetic. Although well-intentioned, the heuristics are half-baked, often creating even stranger results. This is the reason why A1=A2 is return TRUE, but A1-A2=0 is FALSE above. The work-around is to use ROUND prolifically, but prudently. That is, explicitly round results of most arithmetic expressions that might result in fractional digits. Another common alternative is to use expressions of the form ABS(A1-A2)<0.01 when comparing such numbers. But that has obvious limited application, and it can result in surprises because 0.01 cannot be represented exactly in binary floating point. Alternatively, you might consider using the "Precision as displayed" calculation option. I deprecate the use of that feature because of its uncontrolled pervasive effect. If you choose to experiment with it, you should first make a copy of the Excel workbook file. Some changes cause by setting PAD are irreversible. ----- original message ----- "Ad Pronk" wrote in message ... Using Excel 2003 I "discover" a strange thing. Placing brackets around a term introduces an inaccuracy. Is this normal? 1-(1/cos(x)*cos(a.t+x) is nil for t=0 as it should be. But I use this term in a series, so I have to place brackets around the term: (1-(1/cos(x)*cos(a.t+x)) And surprisingly for t=0 I get the answer 1.1 E(-14). It's small but I expect at least a value below E(-22) or 0 Reactions are welcomed Ad Pronk |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Accuracy
"Phil Smith" wrote:
Sorry, but none of that seems to explain his issue I disagree. wrapping a set around an entire formula should NEVER change the results. 1-(1/cos(x)*cos(a.t+x) is mathmaticly equivilent to (1-(1/cos(x)*cos(a.t+x)) Actually, I did address this point by example. But I agree, my explanation was incomplete. I wrote: Moreover, [...] =A1-A2 returns exactly zero. But [...] =(A1-A2) returns about -4.547E-13. [....] A secondary issue is various Excel heuristics (algorithms), which are intended to hide some of the anomalies arise with floating-point arithmetic. Although well-intentioned, the heuristics are half-baked, often creating even stranger results. This is the reason why A1=A2 is return TRUE, but A1-A2=0 is FALSE above. I should have said that is also why =A1-A2 has a different result than =(A1-A2). For some dubious insight, see the section "Example When a Value Reaches Zero" in http://support.microsoft.com/kb/78113 . It is explained incorrectly for general purposes. But ironically, that explanation probably applies directly to the OP's example. Again, since the OP neglected to give specific values, we can only make presumptions. neither decimal/binary conversions issues, or storage issues, or anything should change this. Otherwise, it is a bug. Obviously, MS considers this to be a "feature", not a defect, although the implementation of the feature is "defective" (or at least dubious), IMHO. But I would agree with you to some extent. I wish MS had made it a calculation option (like "Precision as displayed") so that A1-A2 and (A1-A2) had the same results, either always applying or never applying the heuristic to the result of any expression. Note: To clarify, it appears that the heuristic applies only if the __last__ "operator" is subtraction or addition -- and even that description is imprecise, as I recall. Excel is treating closed-parenthesis as an operator in this context(!). Why, with our far more powerful computers and a need for precision, BCD is not at least on optoin, (scalable to any need, potentially accurate to pretty much any precision,) is beyond me. I concur. But BCD is no panacea. Indeed, it can produce "incorrect" results where IEEE floating-point does not. Consider: A1: =1/3 A2: =3*A1 The IEEE floating-point result in A2 is exactly 1. A BCD computer, without additional heuristic, will product 0.9...9 for whatever precision that the user configures. ----- original message ----- "Phil Smith" wrote in message ... Sorry, but none of that seems to explain his issue 2+2 shoudl always be the same as (2+2), regardless of what is actully represented by 2+2. Yes, Parentheses change the order of operations, but only between what is inside and what is outside of those parenthesis. Everything WITHIN the parenthesis should follow it's own OoO, and wrapping a set around an entire formula should NEVER change the results. 1-(1/cos(x)*cos(a.t+x) is mathmaticly equivilent to (1-(1/cos(x)*cos(a.t+x)) neither decimal/binary conversions issues, or storage issues, or anything should change this. Otherwise, it is a bug. I remember the days of the old ATARI 400/800 computers. They used BCD, (binary coded decimal) and to the limits of thier implentation, they were dead on accurate. Why, with our far more powerful computers and a need for precision, BCD is not at least on optoin, (scalable to any need, potentially accurate to pretty much any precision,) is beyond me. Phil Joe User wrote: "Ad Pronk" wrote: Using Excel 2003 I "discover" a strange thing. Placing brackets around a term introduces an inaccuracy. Is this normal? Yes, to some degree. It is questionable whether the parentheses introduce an "inaccuracy" per se. But they certainly can change the result. Since you neglected to include details (klunk!), I cannot reproduce and discuss exactly what you are seeing. But generally, adding parentheses to expressions can change the order of evaluation; the same is true by simply reordering terms. And that can change the final outcome in some cases, depending on the numbers involved. Here is a simple example: A1: =2226-123.36-39.34 A2: =2226-(123.36+39.34) A3: =-123.36-39.34+2226 All results are displayed as 2063.3 in General format. In fact, they even appear identical if you format as Scientific with 14 decimal places, the most number of significant digits (15) that Excel will format. Moreover, =A1=A2 returns TRUE, and =A1-A2 returns exactly zero. But =A1-A2=0 returns FALSE, and =(A1-A2) returns about -4.547E-13. Note that the latter is not an "inaccuracy". In fact, it is the more accurate difference between the values in A1 and A2. Not surprisingly, A3 has exactly the same result as A2, even without additional parentheses. The primary issue is the way that Excel (and most applications) store numbers and perform arithmetic by default. It is called binary floating point -- more specifically, IEEE-754 double-precision floating point. A secondary issue is various Excel heuristics (algorithms), which are intended to hide some of the anomalies arise with floating-point arithmetic. Although well-intentioned, the heuristics are half-baked, often creating even stranger results. This is the reason why A1=A2 is return TRUE, but A1-A2=0 is FALSE above. The work-around is to use ROUND prolifically, but prudently. That is, explicitly round results of most arithmetic expressions that might result in fractional digits. Another common alternative is to use expressions of the form ABS(A1-A2)<0.01 when comparing such numbers. But that has obvious limited application, and it can result in surprises because 0.01 cannot be represented exactly in binary floating point. Alternatively, you might consider using the "Precision as displayed" calculation option. I deprecate the use of that feature because of its uncontrolled pervasive effect. If you choose to experiment with it, you should first make a copy of the Excel workbook file. Some changes cause by setting PAD are irreversible. ----- original message ----- "Ad Pronk" wrote in message ... Using Excel 2003 I "discover" a strange thing. Placing brackets around a term introduces an inaccuracy. Is this normal? 1-(1/cos(x)*cos(a.t+x) is nil for t=0 as it should be. But I use this term in a series, so I have to place brackets around the term: (1-(1/cos(x)*cos(a.t+x)) And surprisingly for t=0 I get the answer 1.1 E(-14). It's small but I expect at least a value below E(-22) or 0 Reactions are welcomed Ad Pronk |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Accuracy
Would you have preferred that
1-(1/cos(x)*cos(a.t+x) also return 1.1E-14 ? It did in Excel versions prior to Excel97. That is the obscure reference to an "optimization" in http://support.microsoft.com/kb/78113 Since 97, if the final operation involves taking the difference between numbers that are equal to 15 figures, then Excel will return zero even if their binary representations are not exactly equal, on the assumption that the actual difference is just the residue of decimal/binary conversions. Wrapping parentheses around the expression means that the subtraction is not viewed as the final operation, so this "optimization" is not applied. It is difficult to be more specific, since the OP's expression is not valid (unmatched parentheses) and involves variables whose values were not stated. IMHO this "optimization" has proved more difficult for people to understand than the what it was intended to "fix", but after more than a decade, it is unlikely that MS will take it back. Jerry "Phil Smith" wrote: Sorry, but none of that seems to explain his issue 2+2 shoudl always be the same as (2+2), regardless of what is actully represented by 2+2. Yes, Parentheses change the order of operations, but only between what is inside and what is outside of those parenthesis. Everything WITHIN the parenthesis should follow it's own OoO, and wrapping a set around an entire formula should NEVER change the results. 1-(1/cos(x)*cos(a.t+x) is mathmaticly equivilent to (1-(1/cos(x)*cos(a.t+x)) neither decimal/binary conversions issues, or storage issues, or anything should change this. Otherwise, it is a bug. I remember the days of the old ATARI 400/800 computers. They used BCD, (binary coded decimal) and to the limits of thier implentation, they were dead on accurate. Why, with our far more powerful computers and a need for precision, BCD is not at least on optoin, (scalable to any need, potentially accurate to pretty much any precision,) is beyond me. Phil Joe User wrote: "Ad Pronk" wrote: Using Excel 2003 I "discover" a strange thing. Placing brackets around a term introduces an inaccuracy. Is this normal? Yes, to some degree. It is questionable whether the parentheses introduce an "inaccuracy" per se. But they certainly can change the result. Since you neglected to include details (klunk!), I cannot reproduce and discuss exactly what you are seeing. But generally, adding parentheses to expressions can change the order of evaluation; the same is true by simply reordering terms. And that can change the final outcome in some cases, depending on the numbers involved. Here is a simple example: A1: =2226-123.36-39.34 A2: =2226-(123.36+39.34) A3: =-123.36-39.34+2226 All results are displayed as 2063.3 in General format. In fact, they even appear identical if you format as Scientific with 14 decimal places, the most number of significant digits (15) that Excel will format. Moreover, =A1=A2 returns TRUE, and =A1-A2 returns exactly zero. But =A1-A2=0 returns FALSE, and =(A1-A2) returns about -4.547E-13. Note that the latter is not an "inaccuracy". In fact, it is the more accurate difference between the values in A1 and A2. Not surprisingly, A3 has exactly the same result as A2, even without additional parentheses. The primary issue is the way that Excel (and most applications) store numbers and perform arithmetic by default. It is called binary floating point -- more specifically, IEEE-754 double-precision floating point. A secondary issue is various Excel heuristics (algorithms), which are intended to hide some of the anomalies arise with floating-point arithmetic. Although well-intentioned, the heuristics are half-baked, often creating even stranger results. This is the reason why A1=A2 is return TRUE, but A1-A2=0 is FALSE above. The work-around is to use ROUND prolifically, but prudently. That is, explicitly round results of most arithmetic expressions that might result in fractional digits. Another common alternative is to use expressions of the form ABS(A1-A2)<0.01 when comparing such numbers. But that has obvious limited application, and it can result in surprises because 0.01 cannot be represented exactly in binary floating point. Alternatively, you might consider using the "Precision as displayed" calculation option. I deprecate the use of that feature because of its uncontrolled pervasive effect. If you choose to experiment with it, you should first make a copy of the Excel workbook file. Some changes cause by setting PAD are irreversible. ----- original message ----- "Ad Pronk" wrote in message ... Using Excel 2003 I "discover" a strange thing. Placing brackets around a term introduces an inaccuracy. Is this normal? 1-(1/cos(x)*cos(a.t+x) is nil for t=0 as it should be. But I use this term in a series, so I have to place brackets around the term: (1-(1/cos(x)*cos(a.t+x)) And surprisingly for t=0 I get the answer 1.1 E(-14). It's small but I expect at least a value below E(-22) or 0 Reactions are welcomed Ad Pronk . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time Accuracy | Excel Discussion (Misc queries) | |||
calculation accuracy | Excel Discussion (Misc queries) | |||
Forecasting Accuracy | Charts and Charting in Excel | |||
Data Accuracy | Excel Discussion (Misc queries) | |||
Accuracy Studies | Excel Discussion (Misc queries) |