Conditional formatting fails on zero value

[KeithS]

Conditionally formatting an Excel 2002 cell fails when the cell value is zero.

I'm checking if the value is greater or equal to the value in another cell,
and colouring the cell green if so, and red if not.
Using 2 lines (1st greater or equal than, pattern = green, 2nd less than,
pattern red) colours the cell red if the cell is blank. To resolve this I
used an additional check as the first line, shunting the others down. The new
first line was 'equal to (ref to blank cell), pattern = no colour. This
resolved the problem, but also caused a no colour cell when the value is 0.

Any ideas?

 

[RBee]

Not sure if this is what you're after, but you might try using an AND or OR
statement to combine the two ideas you want to be green in the first
Conditional formatting statement. And then use...="" to deignate a blank
cell. So if, for example, the two cells were C9 & D9, then the first
Conditional Formatting line in C9 might look something like...
=OR(C9="",C9>=D9) ...to show that the format applies if EITHER the cell is
blank, OR the cell is greater than or equal to the other ref cell.

Then your second Condition would be the less than leading to red.

 

[KeithS]

The ="" is the key. Changing the first line to check for equality with null
or blank (="") makes everything work. The conditional formatting appears to
work on an if/else basis. If the first condition is true, the other
conditions are not evaluated. The problem appears to be that conditional
formatting in Excel equates 0 and the contents of an blank cell, but does not
equate 0 with "".

Just to make it clear:
B7 is blank. Conditionally formatting C9 using 'equals =B7' has the
following results
If C9 = blank, conditional formatting is applied
if C9 = 0, conditional formatting is still applied

Conditionally formatting C9 using 'equals =""' has the following results
If C9 = blank, conditional formatting is applied
if C9 = 0, conditional formatting is not applied

Thanks for your suggestion!

 

[RBee]

Couldn't you still use the "OR" approach within Condition statement 1, and
use all 3 conditions...=OR(xx="",xx=0,xx>=yy)? Basically use the AND or OR
to make the conditional formats come out exactly the way you want them to.
Though, as you point out, these aren't really needed because you can apply
each statement individually with its if/else logic...you are limited to 3
conditions, so sometimes it helps to save a spot or two.

 

[KeithS]

I could - and it would help if I was short of condition lines, but lack this
is not the problem. The issue is the inconsistent equality checking with 0
and blank. The moral is, don't rely on inbuilt equality checks, but build
your own.