Using extra parentheses

[Bernard Liengme]

While experimenting with someone else's data I came across an odd effect.
In A11 I have the numbers:
27 70 591 14

In E1:G1 I have the formulas
=(C1/3.1416)^0.5 =(C1*D1/3.1416)^0.5 =D1*E1^2-F1^2

Giving the values
13.71571 51.31949 and 0 (or 0.00000000000000E00)

Don't worry about the convoluted math or the fact that one can use PI() for
3.1416.

Now copy row 1 to row 2 and modify G2 to read =(D2*E2^2-F2^2). In place of
zero I gets 4.5474735E-13 in G2.

Why does the redundant set of parentheses change the result. IF I use
Evaluate Formula I get 2633.68983957219 - 2633.68983957219 as the
penultimate result in both cases. But one results in zero the other does
not.

Using XL2003 under WIN XP Home
Grateful for any comments

 

[Earl Kiosterud]

My Excel 2002 came up with '1.11022302462516E-16 for the second case.
Quattro Pro came up with 1.11022302462516E-16, same number, for both cases.
Open Office came up with 0.00000000000000000000 for both cases.

 

[Jerry W. Lewis]

Hi, Bernard

You have just rediscovered that most decimal fractions have no exact
binary representation. If you were to chase through the IEEE
representations of the numbers, you would discover that G2 is the
numerically correct answer, but it involves the difference between
numbers that are equal to 15 decimal digits. Presumably in an effort to
avoid questions involving binary math (which may have generated more
questions than it has avoided) MS arbitrarily zeroes the difference of
numbers that are equal to 15 digits, PROVIDED that it is the final
operation in the formula. Wrapping the equation in parentheses makes it
no longer the final operation, and therefore you get to see the actual
numeric result instead of the arbitrarily imposed zero.

Jerry

 

[Jerry W. Lewis]

My Excel 2002 matched Bernard, both as posted, and with the following
equivalent forms
=(14*((591/3.1416)^0.5)^2-((591*14/3.1416)^0.5)^2)
=(14*(SQRT(591/3.1416))^2-(SQRT(591*14/3.1416))^2)

R also agrees using either
14*((591/3.1416)^0.5)^2-((591*14/3.1416)^0.5)^2
or
14*(sqrt(591/3.1416))^2-(sqrt(591*14/3.1416))^2

How did you implement the problem to get different results?

My browser does funny things with the exponentiation character; if yours
does too, then you may have mis-read the formula. I evaluate both
14*((591/3.1416)^0.5)^2 and ((591*14/3.1416)^0.5)^2 to have values of
2633.68983957219, so that Bernard's answer of 4.55E-13 represents a
difference in the 17 decimal figure. Your answer of 1.11E-16 would
represent a difference in the 20th decimal figure, which is a smaller
difference than IEEE double precision can support.

Jerry

 

[Jerry W. Lewis]

Same explanation as I gave Bernard. Neither 1/3 nor 2/3 have exact
binary representations. Chase through the IEEE representations of all
numbers and the successive differences, and you will find the final
answer to be 1/9007199254740992 which in decimal is
1.1102230246251565404236316680908203125E-16

Without the parenthesis, Excel arbitrarily zeros this result because the
final operation takes the difference between two numbers that are equal
to 15 decimal digits.

Jerry

 

[Jerry W. Lewis]

The light finally dawned that you were doing Bucky's substitute problem
rather than Bernard's original problem. I agree with your 1.11E-16
result for the Bucky's problem, so please ignore my previous reply.

I am concerned that Open Office returns zero. What does OO get for
=(1-1/3-2/3)*1E16
=1E16*(1-1/3-2/3)
and
=A1-1E14
where A1 contains 1e14+0.4.

If the final value is 0.4 instead of 0.40625, then OO is probably doing
decimal math instead of binary math. That would be fine, though
possibly slow for very large spreadsheets.

If the final value is 0.40625 and the first two are 0, then OO is
introducing inaccuracies into binary calculations based on assumptions
that may not always be true. That would concern me greatly if I were an
OO user.

Jerry

 

[JE McGimpsey]

OO (1.03 running on a Mac) returns

0.406250000000000

 

[Harlan Grove]

OO (1.03 running on a Mac) returns

0.406250000000000

FTHOI, 123R8 (97 Edition), Gnumeric 1.4.2 *and* OpenOffice Calc 1.9.97
(latest beta for 2.0) all return 0.40625.

It's possible the second poster is using OOo Calc in fixed decimal mode.