I am getting a strange round off error in excel

B

Bernard Liengme

With XL 2003 I get 0 when both G1 and H1 equal 4.
Note the 0 is left aligned since IMSUM gives an imaginary number in text
format.
When G1 and H1 equal -4 the formula correctly yields 2-2i
What do you get and what version of XL are you using?
best wishes
 
R

Ron Rosenfeld

when mixing data types: a real and a complex as in

=IMSUM(-G1/2,IMSQRT(H1))

if G1 and H1 are both exactly equal to 4, the answer whould be 0

see http://www.geocities.com/derekcowley/public/IMerror.xls
and look at location I1 (highlighted).
Click to expand...

In your worksheet, although G1 and H1 appear equal to zero given the limits of
Excel precision, they are not exactly equal to zero.

In particular, if you ROUND the values to 15 decimal places, which is Excel's
precision limit, or if you choose TOOLS/OPTIONS/Calculation/ Precision as
Displayed; you will see the "0" result.


--ron
 
B

Bernard Liengme

This is a good example of where OP did not supply all the data needed to get
a good answer the first time!
 
R

Ron Rosenfeld

This is a good example of where OP did not supply all the data needed to get
a good answer the first time!
Click to expand...

Concur.

And it's an example what happens when we don't know that we don't know
something; or we think we know something but we're wrong.
--ron
 
J

Jerry W. Lewis

Assuming that G4 and/or H4 are calculated values, then look at =(G1-4) and
=(H1-4). I think that you will find that one or both is not equal to 4.

Note that the parentheses are required in these formulas, else Excel will
return zero if they are equal to 15 digits, even though it will calculate
with the full precision non-difference. Similarly, if the differences
between G1 and H1 and 4 are less than 1E14, then formatting the cell to
display more figures will not help, since Excel (as documented) will display
no more than 15 digits; the rest will arbitrarily be zero if you ask for more.

Jerry
 
Top