Excel 2003 IF AND question

[Ducklady]

I created a formula to identify a required referral for purchase orders using
nested IF statements. The formula works well until I reach the lower
authority levels where one employee (level 1) may have $10k authority before
referring to a region manager, but another (level 2) may have $30K before
referring to a region manager. The level 1 employee doesn't make a referral
to the level 2 employee at all. I've tried IF(AND statements but I'm having
difficulty. Suggestions??

Amount of purchase order $45,000
Employee Authority (1,2 or 3)

Authorization required if over $ :
VP Natl Mgr Division Mgr Region Mgr Region Mgr Region Mgr
$100,000 $75,000 $40,000 $30,000 $15,000 $10,000

Refer to : Division Mgr <---- Result

What I've tried
=IF(A1>A5,A4,IF(A1>B5,B4,IF(A1>C5,C4,IF(AND(A1>D5,A2=3,D4)IF(AND(A1>E5,A2=2,D4)IF(AND(A1>F5,A2=1,D4),"Employee"))))))

 

[Brad]

Can you re-arrange the data - example starting in cell a1 - G2
Reg mgr0 Reg mgr2 Reg mgr3 Reg mgr4 Div mgr Nat mgr VP
0 10,000 15,000 30,000 40,000 75,000 100,000

putting 45,000 in H3 and the following formula anywhere

=INDEX($A$1:$G$1,MATCH(H3,$A$2:$G$2))

Copy as needed

 

[Ducklady]

Thank you, it's a good formula, but let's say I need to enter a name for the
region mgr. Picture the result as Steve 1, Steve 2, Steve 3. Doesn't make a
lot of sense. The result needs to look more formal. The employee will enter
their authority level elsewhere on the worksheet. The worksheet should then
tell them who to contact for referral, depending on their individual
authority level and the amount of the contract.

 

[Brad]

After reading your message, is this what you are looking for?

=IF(H6<10000,"No Authority needed",IF(H6<40000,IF(AND(H5=1,H6>10000),"Region
mgr",IF(AND(H5=2,H6>15000),"Region mgr",IF(AND(H5=3,H6>30000),"Region
mgr","No Authority needed"))),INDEX(E1:G1,MATCH(H6,E2:G2))))

Where H5 has the authority level and h6 = purchase order

 

[Brad]

This is a simplier formula that does the same thing
=IF(H6<10000,"No Authority
needed",IF(H6<40000,IF(OR(AND(H5=1,H6>10000),AND(H5=2,H6>15000),AND(H5=3,H6>30000)),"Region mgr","No Authority needed"),INDEX(E1:G1,MATCH(H6,E2:G2))))

 

[Brad]

Having problem getting my posts to stick -

=IF(H6<10000,"No Authority
needed",IF(H6<40000,IF(OR(AND(H5=1,H6>10000),AND(H5=2,H6>15000),AND(H5=3,H6>30000)),"Region mgr","No Authority needed"),INDEX(E1:G1,MATCH(H6,E2:G2))))

Does the same things as my previous post - but less if statements

 

[Ducklady]

That's it! Thank you Brad!!!

After reading your message, is this what you are looking for?

=IF(H6<10000,"No Authority needed",IF(H6<40000,IF(AND(H5=1,H6>10000),"Region
mgr",IF(AND(H5=2,H6>15000),"Region mgr",IF(AND(H5=3,H6>30000),"Region
mgr","No Authority needed"))),INDEX(E1:G1,MATCH(H6,E2:G2))))

Where H5 has the authority level and h6 = purchase order

 

[Joe User]

What I've tried:
=IF(A1>A5,A4,IF(A1>B5,B4,IF(A1>C5,C4,
IF(AND(A1>D5,A2=3,D4)IF(AND(A1>E5,A2=2,D4)
IF(AND(A1>F5,A2=1,D4),"Employee"))))))

Based on the logic that you tried to express above, I infer that the
signature authority depends on a combination of the the dollar amount and, at
some levels, the authority of the employee handling the transaction.

The simplest approach might be simply to correct your syntax and tighten the
logic. It should be:

=IF(A1>A5, A4, IF(A1>B5, B4, IF(A1>C5, C4,
IF(OR(AND(A1>D5,A2=3),AND(A1>E5,A2=2),AND(A1>F5,A2=1)),
D4, "Employee"))))

There might be better ways to do this. For example, if A2 is always 1, 2 or
3 when A1<=C5, then:

=IF(A1>A5, A4, IF(A1>B5, B4, IF(A1>C5, C4,
IF(A1>CHOOSE(A2,D5,E5,F5), D4, "Employee"))))


----- original message -----