Multiple outcomes with mulitple variables

Hi

I am looking for a formula that will solve the problem below. I guess the complexity of the formula might force me down the path of VB but i would really like to have it solved via excel formulas. (my VB is poor!) No doubt it would require multiple nested IF statements.

Can any one please help??

See below:

Consider a matrix.

I have 3 outcomes. Call them x,y,z

Then we have 9 variable running down vertically and 3 variables horizontally.

Let 9 vertical variables be:

1
2
3
4
5
6
7
8
9

For 3 horizontal variables:

A
B
C


For outcome "x" the following conditions are TRUE:

1A
2A
3A
4A
7A
1B
4B


For outcome "y" the following conditions are TRUE:

5A
6A
8A
2B
3B
5B
7B
8B
1C
2C
4C
7C

For outcome "z" the following conditions is TRUE:

9A
6B
9B
3C
5C
6C
8C
9C

I hope this makes sense?

Any help appreciated!

Brendon

Hi

Thanks for looking into this!

Please see attached.

You want:

Amt < 250k and Age < 30 days, do not escalate
Amt 500-1000 and Age < 30 days, do not escalate
Amt >1000 and Age < 30 days, do not escalate

Amt < 250k and Age 30-60 days, do not escalate
Amt 500-1000 and Age 30-60 days, escalate to md
Amt >1000 and Age 30-60 days, escalate to CEO

Amt < 250k and Age > 60 days, escalate to md
Amt 500-1000 and Age > 60 days, escalate to CEO
Amt >1000 and Age > 60 days, escalate to CEO

Something like this?

Thanks for trying Alansidman....sorry the example was not clear.

Mellowmarshall that's exactly what I'm looking for.

If between you guys I could see that in a formula that would solve the problem.

I would prob need to include more variables but once I see the formula I should be able to extrapolate.

Thanks

Ok I think we are close but i think the issue i am having is a limitation of the AND function. My conditions are based off of text NOT integers. i.e

EG:

So what i need is:

IF CELL A1 = "0 - 250k" and CELL B1 = "< 30 days", do not escalate
IF CELL A1 = "500k -1,000k" and CELL B1 = "< 30 days" do not escalate
IF CELL A1 = ">1000k" and CELL B1 = "< 30 days" do not escalate

IF CELL A1 ="0 -250k" and CELL B1 = "30-60 days" do not escalate
IF CELL A1 = "500-1000k" and CELL B1 = "30-60 days" escalate to md
IF CELL A1 = ">1000k" and CELL B1 = "30-60 days" escalate to CEO

IF CELL A1 = "0 - 250k" and CELL B1 = " > 60 days" escalate to md
IF CELL A1 = "500-1000k" and CELL B1 = "> 60 days" escalate to CEO
IF CELL A1 = ">1000k" and CELL B1 = "> 60 days" escalate to CEO

I am receiving an error message saying that i have too many arguments when i try this formula:

+IF(AND(B1="0 - 250k",B2="< 30 DAYS"),"Do not Escalate",IF(AND(B1="250k - 1,000k",B2="< 30 DAYS"),"Escalate to MD",IF(B1="250k - 1,000k",B2="30 - 60 DAYS"),"Escalate to CEO",IF(AND(B1="0 - 250k",B2="30 - 60 DAYS"),"Escalate to CEO",IF(AND(B1="0 - 250k",B2="30 - 60 DAYS"),"Do not Escalate","Escalate to CEO")))))

How do you determine what the last part of the formula should be? i.e. Why would it be "escalate to CEO"?

SORRY ALL! I just realized I was missing an (AND.....

I think its working now!! :-)))))


But still got to test more....

=+IF(AND(U5="0 - 250k",V5="< 30 DAYS"),"No escalation",IF(AND(U5="250k - 1,000k",V5="< 30 DAYS"),"No escalation",IF(AND(U5="> 1,000k",V5="< 30 DAYS"),"No escalation",IF(AND(U5="0 - 250k",V5="30 - 60 DAYS"),"No escalation",IF(AND(U5="250k - 1,000k",V5="30 - 60 DAYS"),"Regional CFO/Head Fin Ops",IF(AND(U5="> 1,000k",V5="30 - 60 DAYS"),"Regional CFO/Head Fin Ops",IF(AND(U5="0 - 250k",V5="30 - 60 DAYS"),"Reinsurance CFO",IF(AND(U5="0 - 250k",V5="30 - 60 DAYS"),"Do not escalate",IF(AND(U5="> 1,000k",V5="> 60 DAYS"),"Reinsurance CFO",IF(AND(U5="<Enter amount>",V5="<Enter amount>"),"No escalation","No escalation"))))))))))