Conditional Format: Cell Value equal to 0 not working?

[Basara]

Hi all,

I'm really confused by an issue i'm facing with 2 simple conditional format rules on a single cell (H6):

Cell Value - Equal to - =0 - Green fill
Cell Value - Not Equal to - =0 - Red fill

When I apply these to H6, the cell is always Red regardless of value, even if I delete the not equal to rule, it still doesn't go green.

H6's value is the sum of 2 other cells and is formatted as currency

I cant understand why its not working.

What's even more confusing is, this sheet is a copy of a template worksheet in the same book , which has the same 2 conditional formatting rules and it WORKS but only only that sheet?!

 

[Yongle]

I tested with these 2 formulas in conditional formatting and it works whether H6 contains direct input OR H6 contains a formula summing 2 other cells

=H6<>0
=H6=0

Try deleting all conditional formatting pertaining to H6 and re-applying

 

[Basara]

I tested with these 2 formulas in conditional formatting and it works whether H6 contains direct input OR H6 contains a formula summing 2 other cells

=H6<>0
=H6=0

Try deleting all conditional formatting pertaining to H6 and re-applying

Hi Yongle, I have tried this already, the only CF for H6 are these 2 entries, other CF rules touch column B only
I've deleted the 2 entries for H6, saved, quit, reloaded, readded,and its still the same.
What I dont understand is why it works in my template sheet but when that is coped to a new sheet, it doesn't?

 

[MARK858]

What does it show if you change the number format on H6 to 15 decimal places

 

[joeu2004]

@Basara.... What happens if you enter the formula =H6=0 into an Excel cell? When the equivalent CF fails to work, does the Excel formula also return FALSE?

I suspect that you are the victim of Excel binary arithmetic "error" (not really; read on), and H6 is not truly zero, even if it might appear to be.

Try one of the following:

1. Change the formula in H6 so that it explicitly rounds the calculation to 2 decimal places (for currency) or whatever number of decimal places that you expect to be accurate.
or
2. Change the CF to the formula =ROUND(H6,2)=0 for the Green condition.

The problem is: most decimal fractions cannot be represented exactly in 64-bit binary floating-point, which Excel uses to represent numbers and perform arithmetic. Also, the binary representation for a particular decimal fraction varies depending on the magnitude of the number.

For example, IF(10.01 - 10 = 0.01, TRUE) returns FALSE(!). But IF(ROUND(10.01 - 10, 2) = 0.01, TRUE) returns TRUE, as expected.

 

[Yongle]

We are both puzzled
- it sounds like the colour format is set to RED for both rules

What happens with only ONE rule?

Delete the RED rule and only use
=H6=0 with GREEN format
Do the cells format correctly ? when H6 = 0 (turns green) AND when H6 <> 0 (no colour)

Then try the RED rule in isolation

The 2 previous posters are on the right track - I did not consider the "is it really zero" question!

 

[Basara]

Hi Joeu2004, you've cracked it,

=H6=0 was returning FALSE
Altered my H6 formula to =ROUND(H4-I6,2) and now it shows TRUE and the CF works!

Thank you, this has been annoying me for months!

 

[Basara]

Thanks to Yongle and Mark as well, missed your answers initially as the notification email took me straight to juen's post