Different results from like formulas

[Biff]

Hi Folks,

This one has me scratching my head! First, I should state
that I'm fully aware of the glitches involved with math
operations being translated from binary to decimal. And I
am also aware of the displayed value versus the true
underlying value. I've read about both here many, many
times. Consider this simple formula:

=5557.27 - 5.04 and the returned value is 5552.23

Now consider this formula:

=IF($A105="","",SUM($H104,$G105)-SUM($D105,$F105))

This formula returns 5552.23000000001

The values used in the second formula are the same values
as the first formula. They are entered manually and are
not calculated and no number formatting is used. So why do
I get different results?

I recently discovered this by adding a new formula in the
sheet:

=IF(AC5=AC7,do something,do something else). Both cells
have the same displayed value yet AC7 has the different
underlying value. Of course that caused the formula to
evaluate to FALSE.

I modified the second formula to:

=IF($A105="","",ROUND(SUM($H104,$G105)-SUM($D105,$F105),2))

Just don't know why two formulas that use the same hard
coded values would return two different results????? Is
this just one of those "things" ?

Biff

 

[J.E. McGimpsey]

AFAIK, it's mainly order of operations - the internal rounding takes
place in a different sequence, which leads to slightly different
results. Sometimes just putting parens around arguments can affect
the 15th decimal digit.

 

[Biff]

Hi J.E.,

Thanks for the feedback. The more I thought about it the
more I came to the conclusion that it must have something
to do with the construct of the formula. Here's the funny
thing though - I have a column that contains over 250 of
the same formula and only that one returns more than 2
decimal places!!!!!

Biff