# Thread: multiple conditions in Conditional format

1. ## multiple conditions in Conditional format

How do I use 2 conditional formats in column B so: Sample attached.

A) Format #1
If no end date [B],
then format #1
I use "cell value is equal to 0"
Fine, now the problem:

B) Format #2 ONLY if:
1) a start date
2) AND no end date,
3) and D3 <0,
then format #2 [B4]

How do I contain all these conditions in a conditional format for Format #2?

I need to avoid evoking format 2 in B5,6,7,etc:
If no start date [and no end date] and D3 <0
It should be format 1, not format 2

Essentially, all cells in column B that have no date should have format 1 [B5,6,7,etc].
Format 2 is evoked only if a start date exists, has no end date and that the avg [D3] has been exceeded [<0]. [B4]

2. You must test your format2 first, then format 1, since if cond1 is true whenever cond2 is true and once cond1 is found to be true, cond2 is never tested (presuming you are pre-XL2007
Cond 1 is the formula:
=AND(A3>0,B3=0,D3<0)

Cond2 is the formula:
=B3=0

Steve

3. Steve,
Thanks. I was trying to do it with "IF".
Can we add a 4th condition? See attachment.
The 4th condition is in column C and is A,B,C or D.
A-D have their own avg-today() in column F and are labeled in column E.

I need CF to pick the proper value/cell in column F to associate with the current/working cell in column B.
In the attached example, I need CF to pick F5 for formatting C4 because the 4th condition is "C".
The actual spreadsheet will not have the cells aligned as in the example.

4. You don't need an if for CF. An IF is for changing the output based on a TRUE/FALSE result. The CF works on a True/False directly. True meets the condition and formats, false does not and then goes to the next condition.

Do you want a 4th conditional format? The answer is no for XL97-2003. It is yes for XL2007 /2010.

DO you want a 4th condition in the AND. That is a YES. But I don't understand from your remarks what you want it to be. Could you elaborate on what colors you want the what cells to be and when they should be that color. [In XL<2007 you are allowed 4 colors: explicit when No CF or all CFs are false or a color for each of 3 possible conditions.]

Steve

5. Steve,
I want a 4th condition in the AND.
Forget the color, I used it only to highlight the cells in question.
I have changed the strings in columns C and E to avoid confusion, ABCD is now WXYZ. See attached Sample #3.xls
The original solution you gave me was based on a single 3rd condition ["avg - today()"], D3. original Sample.xls
=AND(A4>0,B4=0,D3<0)

What if I have 4 different values for 4 different "avg - today()" in column D, [D3, D4, D5, D6].
Move these to column F, [F3, F4, F5, F6]. Sample#2.xls

There are 4 items that have their own "avg - today()" or value in column F
These are labeled in column E [W,X,Y,Z] to differentiate.
Item W (or "avg -today()"#1) [with label E3 or W] is in F3
Item X (or "avg - today()"#2) [with label E4 or X] is in F4
Item Y (or "avg - today()"#3) [with label E5 or Y] is in F5
Item Z (or "avg - today()"#4) [with label E6 or Z] is in F6
Each starting date is for one of these items [W,X,Y,Z] which is labeled/assigned in column C. Column C labels the item associated with its corresponding B to its left.

Now I need to match the cell in column B in question [B4] with the CORRECT "avg - today()" or correct row in column F [F3,F4,F5,F6]. [F5]

This is what I refer to as the 4th condition, matching the cell "Cx" with the corresponding "Fx".

B4 is an item "Y" and the "avg - today()" labeled "Y" is F5. Therefore the value we need to use as the last condition or the ORIGINAL D3 is F5.
For B4 =AND(A4>0,B4=0,F5<0)

For B3 =AND(A4>0,B4=0,F3<0)
For B5 =AND(A4>0,B4=0,F3<0)
For B6 =AND(A4>0,B4=0,F6<0)
For B7 =AND(A4>0,B4=0,F4<0)

How do I program CF to make that determination or match. It seems to be sort of a VLOOKUP or MATCH function to correctly match column B with column F.

6. Forgot attachment. See attached.

7. If I understand you are asking for (locked on A4 and B4)
=AND(\$A\$4>0,\$B\$4=0,VLOOKUP(C3,\$E\$3:\$F\$6,2,0)<0)

Though I suspect you want in B3:
=AND(A3>0,B3=0,VLOOKUP(C3,\$E\$3:\$F\$6,2,0)<0)

And in B4:
=AND(A4>0,B4=0,VLOOKUP(C4,\$E\$3:\$F\$6,2,0)<0)

etc (with A and B not locked on the cell, but relative to the row you are on...
Steve

8. ## The Following User Says Thank You to sdckapr For This Useful Post:

skipro (2011-06-23)

9. Steve,
If I understand you are asking for
Exactly.
I now see the syntax for Vlookup [and AND] in CF and Vlookup in AND.
Thanks.

10. You are very welcome.

Steve

11. Steve,
I have a more complex format to use this. See attached Sample #4.
The "avg-today()" in cols E&F in previous sample #3 is replaced in sample #4 with
a new format.
Instead of the items listed in E and their values in F, now the items and values
are listed in E but on different rows. I think I have that resolved. Please add
any suggestions to my formula. See B3. I modified E4 to show that it works
without the complication below.

My problem is that the item is named [string#1] in C, but the matching item
[string#2] in E, is a phrase which contains the item name [string #1].
string#1 C4 = "Y"
string#2 matching E13 = "if Y"
How do I get "Y" matched to "if Y"?

My thought is to use something like =RIGHT(E12,LEN(E12)-FIND(" ",E12,1)) to
return "Y" from "if Y" but it appears it needs to be less specific and cover the
range and also I do not know how/where to incorporate it in
=OFFSET(INDEX(E3:E17,MATCH(C3,E3:E17,0),1),1,0).

=AND(A3>0,B3=0,INDEX(E3:E17,MATCH("if "&C3,E3:E17,0)+1)<0)

Note: You don't need the "offset", you can just index one more than the match to get the same thing..

Steve

13. Steve,
Mucho gracias. As usual, superb.
Is there a way to copy a thread in this forum other than the obvious copy/paste? The forum should have a copy/print function but I do not see it. I would think people would want a copy of threads.

14. I can do file- print from the browser. With Printpreview I can adjust the size...

Steve

15. Originally Posted by skipro
Steve,
Mucho gracias. As usual, superb.
Is there a way to copy a thread in this forum other than the obvious copy/paste? The forum should have a copy/print function but I do not see it. I would think people would want a copy of threads.
If you have a PDF maker, you could use that to create PDF files of threads of interest.

I use PDF Create (paid) and Bullzip (free) for such purposes.
BullZip is here .... http://www.bullzip.com/products/pdf/info.php

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•