Precision in Excel

R

routeram

Hi,

I find this very puzzling.

I have 2 cells with the following values.

-2.53948423496706E+64 (say =t)
-4.04686094372251E+96 (say = m)

Now I have 2 other cells that have formulae in them. They are
t^3+m^2
t*t*t+(m*m)

Now both these formulas display different results. They are
0
-4.0523E+177

Both cells have 'general' formats.

Although they are not very different, I would prefer the second. Ca
anyone tell me why there is this discrepancy.

Also if I do the same in VBA code. I get a different value of aroun
-2.7e177

Thank
 
J

Jerry W. Lewis

To 15 figures (the limits of Excel's display) t^2 and m^3 are
+/-1.63770834978266E+193

When the last operation in a cell formula is to take the difference of
two numbers that are equal to 15 decimal places, then Excel will
arbitrarily zero the result on the assumption that the non-zero portion
of the difference is round-off error associated with finite precision
calculation and decimal-binary conversion.

Thus =t^3+m^2 will return 0, but =(t^3+m^2) will return
-4.05226129773534E+177

I am at a loss as to why you are getting a non-zero result from
=t*t*t+(m*m) and suspect that there is something you haven't told us.

Jerry
 
R

routeram

Hi,

Thanks. Yes, It was (t*t*t + (m*m)). I thought the outer enclosin
parantheses would make no difference but surely it does!

And do you have any idea about a similar situation in VBA
 
J

Jerry W. Lewis

Excel appears to calculatate the value correctly, and then
post-processes it to arbitrarily return a zero. I am not aware of any
such post-processing that takes place in VBA. Were you asking a general
question, or do you have evidence of similar differences in VBA?

In VBA for Excel XP, t^3+m^2 = (t^3+m^2) and t*t*t+(m*m) = (t*t*t+(m*m))
but neither returns the numeric value that Excel does. More
surprisingly, (t^3+m^2) <> (t*t*t+(m*m)).

I know that paste in the VBA editor behaves differently than in Excel.
If you paste a number of more than 15 digits into Excel, Excel will
truncate the value to 15 digits, while the VBA editor will use the extra
digits to refine the binary approximation, and then display a rounded
(not truncated) 15 digit value. But, in this case, I was pasting 15
digit numbers both places, and will have to think about why I get
different results.

Jerry
 
J

Jerry W. Lewis

I think I mis-spoke. (t*t*t+(m*m)) returns the same value in Excel and
in VBA.

I verified that Excel and VBA are both using the same binary
approximations to t and m.

As for the difference between Excel and VBA in evaluating (t^3+m^2), my
best guess is that one of them calculates x^n as x*x*...x for small
integers n, while the other does it with logarithms, and thus has
slightly different rounding properties. If that is correct, then I
would further guess that VBA always uses logs.

Another possibility is that either Excel or VBA (probably VBA)
interprets the sytax in a way that some intermediate values are held in
the math coprocessor (10-byte storage vs. 8-byte storage) a little
longer, thus changing the rounding properties.

Given the binary approximations to the inputs,
t=15*2^210+3*2^207+7*2^203+3*2^200+255*2^190+11*2^184+3*2^178+2^176+15*2^171+2^169+11*2^163+2^161
m=15*2^317+5*2^312+5*2^304+7*2^298+7*2^294+2^291+3*2^284+3*2^281+7*2^273+2^270
the infinite precision result of (t^3+m^2) is
-3.24580349903661E178
which is surrounded by the VBA and Excel results.

Take home: Excel's math is accurate, but results dependent on more than
15 digit precision are unreliable, as documented in the specifications
for Excel.

Jerry
 
D

Dana DeLouis

More surprisingly, (t^3+m^2) <> (t*t*t+(m*m)).

I really can't add much to this. I get these results in XL vba:

t = -2.53948423496706E+64
m = -4.04686094372251E+96
Debug.Print t ^ 3 + m ^ 2
Debug.Print (t * t * t) + (m * m)

-2.02613064886767E+177
-4.05226129773534E+177

By itself, both of these equations return the same value in vba:

Debug.Print t ^ 3
Debug.Print t * t * t

-1.63770834978266E+193

However, the program Mathematica will almost always show a difference due to
the Power function, and the Times (Multiply) function. (We can't see the
full results of the math coprocessor in Excel)

t^3
-1.6377083497826648*^193

t*t*t
-1.6377083497826650*^193

There is a difference of 2 at the last 17th digit that we normally don't see
in Excel. Looks to me that if Excel knows it is going to do something else
like add an m^2, it will keep the full 17 digits and continue.

I get the same results as VBA.
t^3 + m^2
-2.0261306488676723*^177
t*t*t + m*m
-4.0522612977353450*^177

the infinite precision result of (t^3+m^2) is
-3.24580349903661E178
Click to expand...

I know this isn't important, just interesting. If I give both numbers
infinite precision, I get a slightly different answer:

t=-253948423496706*^50;
m=-404686094372251*^82;

Quick check of Precision & Accuracy

PrecisionAccuracy[{t,m}]
{{Infinity, Infinity}, {Infinity, Infinity}}

N[t^3 + m^2]
-5.472767889977315*^177

If I assign 200 digits of precision to both numbers, I get the same results.

t = -2.53948423496706`200*^64
m = -4.04686094372251`200*^96

N[t^3 + m^2]
-5.472767889977315*^177


Just an interesting topic, that's all.
Dana DeLouis
 
J

Jerry W. Lewis

Thanks for some interesting comments.

Dana DeLouis wrote:

....
If I assign 200 digits of precision to both numbers, I get the same results.

t = -2.53948423496706`200*^64
m = -4.04686094372251`200*^96

N[t^3 + m^2]
-5.472767889977315*^177
Click to expand...


Agreed. Aside to others following the tread: The values that Excel
works with are not

t = -25394842349670600000000000000000000000000000000000000000000000000
m =
-4046860943722510000000000000000000000000000000000000000000000000000000000000000000000000000000000

which give -5.472767889977315*^177 for t^3+m^2, but rather

t = -25394842349670599198922773786532331650736782381239033918667096064
m =
-4046860943722510083660527763632629572061891909961768515851034788285415449899955270904534066528256

which, while more complicated in decimal, are actually simpler in
binary, and give -3.24580349903661E178 when t^3+m^2 is evaluated to
infinite precision.

Excel's answer of -4.05226129773534E+177 is the correct result given
these inputs for
(t*t*t+(m*m))
if every intermediate result is stored as an 8-byte real, with the
requisite rounding that implies.

Jerry
 
Top