software

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=quot;quot;,quot;quot;,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
gt; 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.


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 quot;formula isquot; option and the
formula

=(A14gt;1)*ISNUMBER(A14)--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=528684
One method is to set 3 conditional formats, the first if = quot;quot; (blank) =
no fill colour, the second if = 1 = fill colour Yellow, the third if gt;1
= fill colour red.
Rich Wrote:
gt; I recieved a spreadsheet that contains conditional formatting created
gt; by
gt; another user. The cells subjected to the conditional formatting, which
gt; are
gt; all in one column, contain a simple formula of the form:
gt;
gt; IF(B14=quot;quot;,quot;quot;,IF(L14=0,0,R14/L14))
gt;
gt; The cells were otherwise formatted to display the resulting values as
gt; a
gt; percentage. There are two conditions associated with the conditional
gt; formatting feature. First, if the value in the cell = 1, the cell fill
gt; color
gt; is green. The second condition makes the cell fill color red if the
gt; value is
gt; gt; 1.
gt;
gt; The problem occurs when cell B14 is blank. The formula correctly
gt; results in
gt; the cell being blank; however, the fill color turns to red. Based on
gt; the
gt; conditional formatting conditions, I see no reason for, nor do I want,
gt; the
gt; fill color of the cell to be red. Any help would be much appreciated.--
Door
------------------------------------------------------------------------
Door's Profile: www.excelforum.com/member.php...oamp;userid=33068
View this thread: www.excelforum.com/showthread...hreadid=528684