Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formatting
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 . . . |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formatting
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. "Gdareos" wrote: 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 . . . |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formatting
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),A11200) -- Biff Microsoft Excel MVP "Gdareos" wrote in message ... 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 . . . |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Conditional Formatting
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 ----- On Sat, 8 Mar 2008 14:06:09 -0500, "T. Valko" wrote: 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),A11200) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
Conditional Formatting | Excel Worksheet Functions | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |