conditional formatting

[Guest]

I recieved a spreadsheet that contains conditional formatting created by
another user. The cells subjected to the conditional formatting, which are
all in one column, contain a simple formula of the form:

IF(B14="","",IF(L14=0,0,R14/L14))

The cells were otherwise formatted to display the resulting values as a
percentage. There are two conditions associated with the conditional
formatting feature. First, if the value in the cell = 1, the cell fill color
is green. The second condition makes the cell fill color red if the value is

1.

The problem occurs when cell B14 is blank. The formula correctly results in
the cell being blank; however, the fill color turns to red. Based on the
conditional formatting conditions, I see no reason for, nor do I want, the
fill color of the cell to be red. Any help would be much appreciated.

 

[daddylonglegs]

The problem is that in Excel's ranking system a blank is deemed to be
bigger than any number, hence the formatting.

One way round this would be to change your second condition. If the
cell that you're formatting is A14 use the "formula is" option and the
formula

=(A14>1)*ISNUMBER(A14)

 

[Door]

One method is to set 3 conditional formats, the first if = "" (blank) =
no fill colour, the second if = 1 = fill colour Yellow, the third if >1
= fill colour red.