Basic Conditional Formatting seems to give inconsistent results for zeroes.

Hi Everyone,

I'm using conditional formatting in a very basic way. It's not even a complicated rule I'm using, just the basic "from the menu" greater than and less than formatting from the Conditional Formatting > Highlight Cell Rules dropdown menu.

Green for values greater than zero, red for values less than zero. No other modifications or anything. The intention is to have the exact zero show up without any highlighting at all. Simple!

In practice, however, the greater-thans ARE green, the lesser-thans ARE red... BUT, the zeros aren't really behaving. Most of them are clear, but some are showing red, and some are showing green.

zeroes1.JPG zeroes2.JPG

I ran the conditional formatting on the whole sheet at once, and I only ran those two rules, so it shouldn't be a conflict of any sort. The fields are formatted as "Number" fields, but I tried as "Currency" as well and they still cause the highlighting. They all contain basic formulas to calculate the values they display, but I just can't figure out why some zeroes are counted as zero, some are counted positive, and some are counted negative. They're all just zero!

Any ideas would be appreciated!

***UPDATE***

Okay, so on a hunch from re-reading that I just wrote "they're all just zero" .... I thought that maybe they might not actually BE zeroes, but that they might just LOOK LIKE zeroes. I manually entered a "0" into one of the highlighted cells just to see, and it DID change to white.

I undid that change and put it back to the formula that's calculating the actual values, and it went back to red again. So I widened the column out and increased the decimal display out to 13 decimal places and found a sneaky non-zero value!

zerodecimal.JPG

Is there any way to get Excel to not consider this six-trillionth of a cent as being a positive value, so that it will format it as a zero?

I'm happy for anything less than a cent to be assumed as zero, but how do I tell Excel this?

Genius Glenn! Thanks for the reply!

I put it in as =ROUND(SUM(U32,X32),2) and it seems to have done the trick. All my zeroes are zeroes! (Which, incidentally, is a great name for a rock album!)

Cheers, and thanks again!
~Joe