Conditional Formatting

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gdareos - 08 Mar 2008 18:42 GMT

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 . . .

Reply to this Message

Sean Timmons - 08 Mar 2008 19:05 GMT

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.

> I'm new to conditional formatting, and only have Excel 2003.
>
[quoted text clipped - 21 lines]
>
> gDareos on a beautiful Saturday, chained to my computer . . .

Reply to this Message

T. Valko - 08 Mar 2008 19:06 GMT

> Green if a value > 1200
>I use "N/A", but it keeps showing Green.

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)

Signature

Biff
Microsoft Excel MVP

> I'm new to conditional formatting, and only have Excel 2003.
>
[quoted text clipped - 21 lines]
>
> gDareos on a beautiful Saturday, chained to my computer . . .

Reply to this Message

Gdareos - 15 Mar 2008 20:42 GMT

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
-----

>> Green if a value > 1200
>>I use "N/A", but it keeps showing Green.
[quoted text clipped - 7 lines]
>
>=AND(COUNT(A1),A1>1200)

Reply to this Message

Rate this thread: