Code for 9 conditions in a Cell

[[email protected]]

Not Sure if Im in the right thread. Im looking for a code to run on a column.

e.g
Cells in Column D = Low, Medium, or High
Cells in Column E = Low, Medium, or High

Column F should have the following conditions:


1. Green: LL(Low Probability, Low Impact), or LM (Low Probability, Medium Impact), or ML (Medium Probability, Low Impact)

2. Yellow: LH (Low Probability, High Impact), or MM (Medium Probability, Medium Impact), or HL (High Probability, Low Impact)

3. Red: MH (Medium Probability, or High Impact), or HM (High Probability, Medium Impact), or HH (High Probability, High Impact).


Please help.

Thanks

[Pete_UK]

Assuming your data is in D2 and E2 (allowing for headers), then put this in F2:

=IF(OR(LEFT(D2)&LEFT(E2)="LL",LEFT(D2)&LEFT(E2)="ML",LEFT(D2)&LEFT(E2)="LM"),"Green",IF(OR(LEFT(D2)&LEFT(E2)="HH",LEFT(D2)&LEFT(E2)="MH",LEFT(D2)&LEFT(E2)="HM"),"Red","Yellow"))

Hope this helps.

Pete

[[email protected]]

Sample.xlsxHello Pete,

Thanks for the quick response., Im not sure i made everything as elaborate as possible.

I have attached a sample document with the exact situation.


I want column C to be automatically populated with the content it has right now if the conditions in Columns A and B are met.

|Find the sample attached. Thank you soooo much

Sample.xlsx

[Ace_XL]

=IF(OR(AND(A2="Low",B2="Low"),AND(A2="Low",B2="Medium"),AND(A2="Medium",B2="Low")),"Green",
IF(OR(AND(A2="Low",B2="High"),AND(A2="Medium",B2="Medium"),AND(A2="High",B2="Low")),"Yellow",
IF(OR(AND(A2="Medium",B2="High"),AND(A2="High",B2="Medium"),AND(A2="High",B2="High")),"Red","")))

[[email protected]]

Hello Ace, Thanks for the prompt response, I tried the formula and some of the cells seem to have been missed in the calculations. They just came back as blank.

I attach a second sample file with the solution you suggested..

Sample2.xlsx

[Ace_XL]

The ones that have returned as blank have trailing spaces in the first column i.e. "High " instead of "High"

delete those spaces and the formula will work fine

[popipipo]

Remove the spaces in the cells A3, A4,....

to late

[[email protected]]

GENIUS!!!! thanks fellows

[[email protected]]

Does anyone know how to close this thread?

[Pete_UK]

You said that your data was in column D and E and that you wanted the formula in column F. Change my formula to this:

=IF(OR(LEFT(A2)&LEFT(B2)="LL",LEFT(A2)&LEFT(B2)="ML",LEFT(A2)&LEFT(B2)="LM"),"Green",IF(OR(LEFT(A2)&LEFT(B2)="HH",LEFT(A2)&LEFT(B2)="MH",LEFT(A2)&LEFT(B2)="HM"),"Red","Yellow"))

with your data in A and B.

To mark the thread as Solved, click on Thread Tools above your first post.

Hope this helps.

Pete