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

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

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

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

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

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

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

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


.