Formula answer red when should be black

[Dave]

I have a worksheet error testing formula that subtracts
one number from another in a cell thats formated to show
red when the answer is less than zero. If I have not made
an entry error the two numbers are the same so the answer
is zero and the zero showes up in red. If I reverse the
formula the answer is still zero but shows up in black. My
first thought was that sometimes in Excel during
computations a number might be added to one of the numbers
well to the right of the decimal. I checked for this, to
15 digits past the decimal and it's still all zeros. In
another simular spreadsheet with the same test formula the
answer shows up red when the formula is reversed. Can
someone explain a solution so only red when the answer is
less than zero and if the two numbers being subtracted
from each other are the same the zero will be black?
Thanks Dave

 

[Jay]

Dave,

Send your spreadsheet to: (e-mail address removed) I'll
correct it for you.

Thanks,
Jay Gustafson

 

[hcj]

I have a feeling you're getting caught by the underlying
binary math the spreadsheet does to calculate with. Your
numbers look fine in decimal but they have that hidden
binary personality. This is particularly true for
calculated numbers rather than those entered directly into
the sheet. If the latter is the case, you might try
putting the entire calculation inside a =round
(calc,precision) function, forcing number truncation at a
certain decimal place.
Otherwise try forcing the subtraction precision by saying:
=Round(num1,15)-round(num2,15)
or some convenient number of decimal places beyond your
actual precision.
Good luck.