Conditional Formatting

[wiscokid]

I am trying to set a cell equal to blank and keep my conditional formatting statements from being triggered (cell color left white)

The issue I am having is for the following formula:

=IF(D5<0.02,,D5)
this sets the cell = 0.0 if d5<.02
(I want the cell to be blank, not contain 0.0)

If I use
=IF(D5<0.02," ",D5)
then it makes the cell blank (which I want), but my
conditional formatting makes the cell green
my conditional formatting is:
If cell >.10 then green
If cell between .05 and .10 then blue
if cell between .02 and .05 then yellow

If the cell is less than .02, I would like the cell to be blank and not be formatted (kept white)

any ideas?

 

[Oaktree]

Use =IF(D5<0.02,"",D5) to make your blanks.

Then, in the conditional format condition declaration, instead of using cell value is, try formula is =AND(ISNUMBER(A1),A1>0.1) where A1 is the activecell to which you're applying the format. The ISNUMBER function should take care of your blanks.

(Similarly, try formula is =AND(ISNUMBER(A1),A1>0.05,A1<=.10) for the blue condition.)

 

[Barry Katcher]

Yes, I see what you mean; don't know why it's doing that. To fix, make your first conditional statement If cell is between .10 and 1000 or some other outlandishly high number that will never be reached.

EDIT: My solutions is a work-around, Oaktree's is a direct solution - use his (but pay me).

 

[Oaktree]

My solutions is a work-around, Oaktree's is a direct solution - use his (but pay me).

You can have 30% of my $0.