Conditional Formatting

  • Thread starter Thread starter Gdareos
  • Start date Start date
G

Gdareos

I'm new to conditional formatting, and only have Excel 2003.

I don't have a problem getting my 3 desired rules entered and
functioning.

Red if a value <800
Yellow if a value falls between 800.01 and 1200
Green if a value > 1200

My problem is that if there is no data yet (such as for months that
have not occurred, and thus there is no value, I use "N/A", but it
keeps showing Green.

I do not want any formatting applied if the value is "N/A".

I just inherited some Excel spreadsheets, and I have to make some
changes, but in the process, I found I don't know how to prevent
formatting from occurring for the "N/A" values.

Thanks for any help -- I have Googled this like crazy, but can't find
this topic referenced. Naturally, my deadline is Monday morning and
this is Saturday and I've been working on this since 5 AM and not
having much luck.

gDareos on a beautiful Saturday, chained to my computer . . .
 
Try this.. In the first format, enter value is between 0.1 and 800 format
red. Then, in second, indicate if less than or equal to 1200, format yellow,
in last enter as grater than 1200 format green. Then, ensure you have the
cell formatted as no fill by default.
 
Green if a value > 1200
I use "N/A", but it keeps showing Green.
Click to expand...

In Excel a TEXT value will evaluate to be greater than *any* numeric value.
So, if the cell value is "N/A":

"N/A" > 1200 = TRUE and the format is applied.

You have to test that the value is indeed a number. Something like this:

=AND(COUNT(A1),A1>1200)
 
Thanks! This got me pointed in the right direction.

I found that if I set the value between .80 and 1000 (I chose a big
number that couldn't occur, that this kept the "N/A" on a white
background, and anything obove .80 applied the desired Conditional
Formatting.

Boy, I was baffled!

This is a great newsgroup, indeed!

Thanks again
-----
 
Back
Top