|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 3rd, 2010, 08:42 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 IF AND question
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(A1A5,A4,IF(A1B5,B4,IF(A1C5,C4,IF(AND(A1D5, A2=3,D4)IF(AND(A1E5,A2=2,D4)IF(AND(A1F5,A2=1,D4) ,"Employee")))))) 
|
|
#2
May 3rd, 2010, 08:55 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2003 IF AND question
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 -- Wag more, bark less "Ducklady" wrote: 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(A1A5,A4,IF(A1B5,B4,IF(A1C5,C4,IF(AND(A1D5, A2=3,D4)IF(AND(A1E5,A2=2,D4)IF(AND(A1F5,A2=1,D4) ,"Employee"))))))
|
|
#3
May 3rd, 2010, 09:21 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2003 IF AND question
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" wrote: 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 -- Wag more, bark less "Ducklady" wrote: 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(A1A5,A4,IF(A1B5,B4,IF(A1C5,C4,IF(AND(A1D5, A2=3,D4)IF(AND(A1E5,A2=2,D4)IF(AND(A1F5,A2=1,D4) ,"Employee"))))))
|
|
#4
May 3rd, 2010, 09:44 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2003 IF AND question
After reading your message, is this what you are looking for? =IF(H610000,"No Authority needed",IF(H640000,IF(AND(H5=1,H610000),"Region mgr",IF(AND(H5=2,H615000),"Region mgr",IF(AND(H5=3,H630000),"Region mgr","No Authority needed"))),INDEX(E1:G1,MATCH(H6,E2:G2)))) Where H5 has the authority level and h6 = purchase order -- Wag more, bark less "Ducklady" wrote: 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" wrote: 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 -- Wag more, bark less "Ducklady" wrote: 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(A1A5,A4,IF(A1B5,B4,IF(A1C5,C4,IF(AND(A1D5, A2=3,D4)IF(AND(A1E5,A2=2,D4)IF(AND(A1F5,A2=1,D4) ,"Employee"))))))
|
|
#5
May 3rd, 2010, 09:51 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2003 IF AND question
This is a simplier formula that does the same thing
=IF(H610000,"No Authority needed",IF(H640000,IF(OR(AND(H5=1,H610000),AND(H 5=2,H615000),AND(H5=3,H630000)),"Region mgr","No Authority needed"),INDEX(E1:G1,MATCH(H6,E2:G2)))) -- Wag more, bark less "Brad" wrote: After reading your message, is this what you are looking for? =IF(H610000,"No Authority needed",IF(H640000,IF(AND(H5=1,H610000),"Region mgr",IF(AND(H5=2,H615000),"Region mgr",IF(AND(H5=3,H630000),"Region mgr","No Authority needed"))),INDEX(E1:G1,MATCH(H6,E2:G2)))) Where H5 has the authority level and h6 = purchase order -- Wag more, bark less "Ducklady" wrote: 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" wrote: 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 -- Wag more, bark less "Ducklady" wrote: 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(A1A5,A4,IF(A1B5,B4,IF(A1C5,C4,IF(AND(A1D5, A2=3,D4)IF(AND(A1E5,A2=2,D4)IF(AND(A1F5,A2=1,D4) ,"Employee"))))))
|
|
#6
May 3rd, 2010, 09:54 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2003 IF AND question
Having problem getting my posts to stick -
=IF(H610000,"No Authority needed",IF(H640000,IF(OR(AND(H5=1,H610000),AND(H 5=2,H615000),AND(H5=3,H630000)),"Region mgr","No Authority needed"),INDEX(E1:G1,MATCH(H6,E2:G2)))) Does the same things as my previous post - but less if statements -- Wag more, bark less "Ducklady" wrote: 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" wrote: 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 -- Wag more, bark less "Ducklady" wrote: 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(A1A5,A4,IF(A1B5,B4,IF(A1C5,C4,IF(AND(A1D5, A2=3,D4)IF(AND(A1E5,A2=2,D4)IF(AND(A1F5,A2=1,D4) ,"Employee"))))))
|
|
#7
May 3rd, 2010, 10:01 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2003 IF AND question
That's it! Thank you Brad!!!
"Brad" wrote: After reading your message, is this what you are looking for? =IF(H610000,"No Authority needed",IF(H640000,IF(AND(H5=1,H610000),"Region mgr",IF(AND(H5=2,H615000),"Region mgr",IF(AND(H5=3,H630000),"Region mgr","No Authority needed"))),INDEX(E1:G1,MATCH(H6,E2:G2)))) Where H5 has the authority level and h6 = purchase order -- Wag more, bark less "Ducklady" wrote: 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" wrote: 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 -- Wag more, bark less "Ducklady" wrote: 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(A1A5,A4,IF(A1B5,B4,IF(A1C5,C4,IF(AND(A1D5, A2=3,D4)IF(AND(A1E5,A2=2,D4)IF(AND(A1F5,A2=1,D4) ,"Employee"))))))
|
|
#8
May 3rd, 2010, 10:09 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2003 IF AND question
"Ducklady" wrote:
What I've tried: =IF(A1A5,A4,IF(A1B5,B4,IF(A1C5,C4, IF(AND(A1D5,A2=3,D4)IF(AND(A1E5,A2=2,D4) IF(AND(A1F5,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(A1A5, A4, IF(A1B5, B4, IF(A1C5, C4, IF(OR(AND(A1D5,A2=3),AND(A1E5,A2=2),AND(A1F5,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(A1A5, A4, IF(A1B5, B4, IF(A1C5, C4, IF(A1CHOOSE(A2,D5,E5,F5), D4, "Employee")))) ----- original message ----- "Ducklady" wrote: 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(A1A5,A4,IF(A1B5,B4,IF(A1C5,C4,IF(AND(A1D5, A2=3,D4)IF(AND(A1E5,A2=2,D4)IF(AND(A1F5,A2=1,D4) ,"Employee"))))))
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
