"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