Conditional formatting confusion when cell is empty

[r_ivica]

i have this conditional format:

Condition 1:
Formula =AND($R$5>=$H$5;$H$5<>0)
RED COLOR
Condition 2:
Value of cell is smaller than =$H$5
GREEN COLOR


So this works ok, but problem is when in R5 is empty cell he gives me red color but i want when cell is empty - no to have that cell colored - i want to be white as default!

How to solve this?

 

[xenou]

Condition 1 Formula Is:
=(($R$5>=OFFSET($R$5,0,-10))*($H$5<>0)*(NOT(ISBLANK($R$5))))

Condition 2 Formula Is:
Change the >= to <

The use of multiplication relies on the fact that "false" is numerically 0, so if anything is false the whole value becomes 0 (anything times zero is zero), which means the whole statement is zero, so "false".

I recommend using the Offset formula in conditionally formatting so that inserting rows doesn't give you reference errors in the formula - everything depends only on the one cell the formula is in (in this case, it looks ten rows to the left, rather than at column H per se).

It may be easier to view these formulas in a regular cell if you want to take them apart.

 

[xenou]

BTW always watch out for the effect of empty cells on formula. So far as my tests go, a blank cell is greater than 0 and also less than 0 -- you can imagine how this throws conditional testing out the window. Regards

 

[r_ivica]

not sure if i understand this formulas but i inserted it right as you told me here, and still have red color when cell is empty!?

 

[r_ivica]

but now i see what is potential problem. This cell R5 is linked to another cell where is this formula:

=IF(Q104="";"";MROUND((Q104);CHOOSE(MATCH((Q104);{1,01;2;3;4;6;10;20;30;50;100});0,01;0,02;0,05;0,1;0,2;0,5;1;5;10)))

so in R5 is actually =R104 where is this formula above.

So when i delete this cell with Delete on my keyboard it really gives me white format as i want because that cell IS empty as excel see, but when have this inside =R104 which is linked to those R104 cell where is empty cell - according to formula provided above where is "" condition - excel don't format right as empty!

any new ideas how to solve this? it would help me lot!

 

[barry houdini]

Try

=AND($R$5>=$H$5;$H$5<>0;$R$5<>"")

 

[r_ivica]

Try

=AND($R$5>=$H$5;$H$5<>0;$R$5<>"")

working great! thanks to all for helping :wink: