multiple conditions in Conditional format

Topic

New Reply

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

A) Format #1
If no end date ,
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]

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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,D30,B4=0,F50,B4=0,F30,B4=0,F30,B4=0,F60,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.

Reply | Quote

Forgot attachment. See attached.

Reply | Quote

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,B3=0,VLOOKUP(C3,$E$3:$F$6,2,0)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

Reply | Quote

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.

Reply | Quote

You are very welcome.

Steve

Reply | Quote

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

Reply | Quote

How about:

=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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Steve

Reply | Quote