No problem. A concrete example should help cement the concepts for you.

Using the D2D VBA function that I recommended, you will see that 0.5 is
represented exactly (2^-1 obviously has an exact binary representation),
while the approximations to 0.4 and 0.1 are
0.400000000000000022204460492503130808472633361816 40625
0.100000000000000005551115123125782702118158340454 1015625
If you do the math, you will see that =0.5-0.4 (using the approximation to
0.4) is exactly
0.099999999999999977795539507496869191527366638183 59375
which with Excel's 15 digit display limit displays as 0.1, even though the
exact value is different than the value you got by entering 0.1 directly.
Thus, the exact result of
=0.5-0.4-0.1-0 or =(0.5-0.4-0.1),
which is also the value used by
=IF(0.5-0.4-0.1=0, €¦
is
-2.77555756156289135105907917022705078125E-17
But, since the two different values that both display as 0.1 agree to 15
decimal digits, the "optimization" kicks in for
=0.5-0.4-0.1
and returns 0, even though the two numbers are not exactly equal.

I suspect that the seemingly inconsistent math that results from this
"optimization" has produced more questions than the "optimization" avoids,
hence my contention that the "optimization" was an unfortunate decision.

Jerry

"Ginger" wrote:

JoeU and Jerry, thanks again for your help - maybe you can further clarify
one point.
I have read your answers and explored the links you provided (and spent way
too much time on this, I'm sure!) And I get the whole binary number thing
(who knew), but I think it is the "opitmization" part that is throwing me.
Basically, to use another example:
.5 - .4 - .1 = 0,
then why does .5 - .4 - .1 - 0 = -.0000000000000000277555756...
I assume the first example which returned 0, "really" returns the odd value,
but Excel has "optimized" the result and so corrects the answer to be 0.(?)
What I don't get is why the second example which only has the extra
operation of - 0 at the end, doesn't also get "optimized/corrected"?


"Ginger" wrote:

Wow - thanks JoeU, Jerry and Alan for the help!!
Honestly, I'll have to re-read your answers in order to (better) understand
them, but you have really made my day to know that there is logic behind what
happened!
As I stated, I had planned to override the formula in this row and move on,
but just needed to know there was a reason for it acting so strangely.
I noticed the problem in the first place, because in col. I, I have an IF
statement: IF(H1=0,"COMPLETE","still active"). So, knowing this project was
complete, and already billed in full, I didn't know why col. I was saying
"Still active".
That's when I expanded the decimals to see that it was indeed not 0.
I will try your options for fixing the formula, and re-post to tell you how
it worked.