D
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"))))))
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"))))))