Conditional Formatting - Color Exceptions

**Falk781** · 08-11-2009, 07:23 PM

When using conditional formatting, I have it set to the following:

C2=60 (Reference Cell)
C3=5 (Reference Cell)

Conditional Format Settings
If C3 >= C2/12 then pattern set to Green
If C3 < C2/12 then pattern set to Red

Here is the problem - when you have a value in C3, everything is fine, it's either green or red. If C3 is blank, it defaults to green because the conditional format is true.

Question - Is there anyway to add something in there to have no color when C3 is not populated?

**Cheeky Charlie** · 08-11-2009, 07:35 PM

Cheap way:
Make your first conditional format check to see if cell="" and set no format for this condition, your original formats would be two and three

Neater way:
Set your conditional format as formula is =and(not(isblank(c3)),c3">="C2/12)

**Falk781** · 08-11-2009, 08:12 PM

When I set the Cf to "Formula" and put in your formula, it says that "my formula contains an error." If I use the "cheap way" it still doesn't seem to be working. See the attached picture for illustration.

**Falk781** · 08-12-2009, 10:33 AM

Ok, I got the ("") working to make it white... Setting it to no format doesn't seem to work. I had to change the pattern to white. I still can't get that formula to work. Any ideas?

See the modified attachment.

**oldchippy** · 08-12-2009, 10:53 AM

Hi,

Try this one

**Falk781** · 08-12-2009, 01:29 PM

Thanks for replying to my problem. My CF works the way I set it up in the picture, the problem is, I can't get the proposed formula to work, or the "neat" way.

in other words, this formula: =and(not(isblank(c3)),c3">="C2/12) doesn't work.

**Cheeky Charlie** · 08-12-2009, 01:41 PM

hey, sorry, take the quotes out form around >= - that was a result of testing it in a different environment.

CC

**Falk781** · 08-12-2009, 02:11 PM

CC,

Was worth a try and it fixed the error, but now the conditional formatting doesn't work at all. I suppose I should stick with the "Cheap Way", because it seems to work?

**oldchippy** · 08-12-2009, 06:03 PM

Hi,

Try this one then

**Falk781** · 08-13-2009, 10:18 AM

Looks like this one works. Thanks alot for your help.

What is the and/not/isblank part telling it to do?

and(not(isblank

**Cheeky Charlie** · 08-13-2009, 11:03 AM

build it up from the middle function
isblank('any cell') is true if 'any cell' is blank
not('anything') is true if 'anything' is false, and false if 'anything' is true
and('one thing', 'another thing') is true if both 'one thing' and 'another thing' are true and false otherwise

CC

**oldchippy** · 08-13-2009, 11:09 AM

Hi,

=AND(NOT(ISBLANK(C3))),C3<=C2/12

ISBLANK(C3) checks whether the cell is blank - returns TRUE or FALSE

NOT changes FALSE to TRUE and visa versa

AND checks whether NOT(ISBLANK(C3)) and C3<=C2/12 are TRUE or FALSE

Conditional Formatting - Color Exceptions

LinkBack

Thread Tools

Rate This Thread

Display

Conditional Formatting - Color Exceptions

Re: Conditional Formatting - Color Exceptions

Re: Conditional Formatting - Color Exceptions

Re: Conditional Formatting - Color Exceptions

Re: Conditional Formatting - Color Exceptions

Re: Conditional Formatting - Color Exceptions

Re: Conditional Formatting - Color Exceptions

Re: Conditional Formatting - Color Exceptions

Re: Conditional Formatting - Color Exceptions

Re: Conditional Formatting - Color Exceptions

Re: Conditional Formatting - Color Exceptions

Re: Conditional Formatting - Color Exceptions

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions