| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex If/And Statement
I'm stuck! I can't find a way to get this complex formula to work. First,
where am I going wrong? And second, is there an easier way to write it? I'm guessing I have too many arguements. HELP! =IF(AND(G8=0,H8=0%),"Successful",IF(AND(G8=1,H8= 30%),"Successful",IF(AND(G8=2,H8=40%),"Successful ",IF(AND(G8=3,H8=50%),"Successful",IF(AND(G8=4,H8 =65%),"Successful",IF(AND(G8=5,H8=75%),"Successf ul",IF(AND(G8=6,H8=90%),"Successful",IF(AND(g8=6 ,h8<=80%),"DoesNotMeet",IF(AND(G8=6,<=89%,"Incons istent",IF(AND(G8=6,H8<=114%"Successful",IF(AND(G 8=6,H8<=124%,"Excellent",IF(AND(G8=6,H8=125%,"E xceptional")))))))))))) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complex If/And Statement
Try this:
=IF(OR(AND(G8=0,H8=0%),AND(G8=1,H8=30%),AND(G8=2 ,H8=40%),AND(G8=3,H8=50%),AND(G8=4,H8=65%),AND( G8=5,H8=75%)),"Successful",IF(AND(G8=6,H8<=80%), "DoesNotMeet",IF(AND(G8=6,H8<=89%),"Inconsistent" ,IF(AND(G8=6,H8<=114%),"Successful",IF(AND(G8=6, H8<125%),"Excellent",IF(AND(G8=6,H8=125%),"Excep tional","???")))))) You can only nest a maximum of seven IF statements within one formula. Since lots of your pairs of conditions yield the same result ("Successful"), we can combine them within an OR statement. Also, there are many combinations of values for G8 & H8 which fall outside your rules (maybe they can never occur in your paricular worksheet). I have set the formula above to return ??? in this case. Hope this helps, Hutch "jonssmaster" wrote: I'm stuck! I can't find a way to get this complex formula to work. First, where am I going wrong? And second, is there an easier way to write it? I'm guessing I have too many arguements. HELP! =IF(AND(G8=0,H8=0%),"Successful",IF(AND(G8=1,H8= 30%),"Successful",IF(AND(G8=2,H8=40%),"Successful ",IF(AND(G8=3,H8=50%),"Successful",IF(AND(G8=4,H8 =65%),"Successful",IF(AND(G8=5,H8=75%),"Successf ul",IF(AND(G8=6,H8=90%),"Successful",IF(AND(g8=6 ,h8<=80%),"DoesNotMeet",IF(AND(G8=6,<=89%,"Incons istent",IF(AND(G8=6,H8<=114%"Successful",IF(AND(G 8=6,H8<=124%,"Excellent",IF(AND(G8=6,H8=125%,"E xceptional")))))))))))) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complex If/And Statement
Thank you so much. That worked Great.
"Tom Hutchins" wrote: Try this: =IF(OR(AND(G8=0,H8=0%),AND(G8=1,H8=30%),AND(G8=2 ,H8=40%),AND(G8=3,H8=50%),AND(G8=4,H8=65%),AND( G8=5,H8=75%)),"Successful",IF(AND(G8=6,H8<=80%), "DoesNotMeet",IF(AND(G8=6,H8<=89%),"Inconsistent" ,IF(AND(G8=6,H8<=114%),"Successful",IF(AND(G8=6, H8<125%),"Excellent",IF(AND(G8=6,H8=125%),"Excep tional","???")))))) You can only nest a maximum of seven IF statements within one formula. Since lots of your pairs of conditions yield the same result ("Successful"), we can combine them within an OR statement. Also, there are many combinations of values for G8 & H8 which fall outside your rules (maybe they can never occur in your paricular worksheet). I have set the formula above to return ??? in this case. Hope this helps, Hutch "jonssmaster" wrote: I'm stuck! I can't find a way to get this complex formula to work. First, where am I going wrong? And second, is there an easier way to write it? I'm guessing I have too many arguements. HELP! =IF(AND(G8=0,H8=0%),"Successful",IF(AND(G8=1,H8= 30%),"Successful",IF(AND(G8=2,H8=40%),"Successful ",IF(AND(G8=3,H8=50%),"Successful",IF(AND(G8=4,H8 =65%),"Successful",IF(AND(G8=5,H8=75%),"Successf ul",IF(AND(G8=6,H8=90%),"Successful",IF(AND(g8=6 ,h8<=80%),"DoesNotMeet",IF(AND(G8=6,<=89%,"Incons istent",IF(AND(G8=6,H8<=114%"Successful",IF(AND(G 8=6,H8<=124%,"Excellent",IF(AND(G8=6,H8=125%,"E xceptional")))))))))))) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complex If/And Statement
I would use VLOOKUP here and with two tables: one for G8=6 and one
for G8<6. Table 1: 0 DoesNotMeet 80% Inconsistent 90% Successful 115% Excellent 125% Exceptional Table 2: 0 0 1 30% 2 40% 3 50% 4 65% 5 75% Now your formula is compacted as follows, for the cases that G8 is < 6 and for when it is = 6. =IF(G8<6,IF(H8VLOOKUP (G8,Table2,2),"Successful","UNSUCCESSFUL"),VLOOKUP (H8,Table1,2)) Notes: I have added the "UNSUCCESSFUL" because your formula did not say what would happen if, e.g. G8=4 and H8<65%. Table1 and Table2 will be cell ranges like e.g. A1:B5 or C4:D9 HTH Kostis Vezerides On Nov 28, 7:36*pm, jonssmaster wrote: I'm stuck! I can't find a way to get this complex formula to work. First, where am I going wrong? And second, is there an easier way to write it? *I'm guessing I have too many arguements. *HELP! =IF(AND(G8=0,H8=0%),"Successful",IF(AND(G8=1,H8= 30%),"Successful",IF(AND(G8=2,H8=40%),"Successful ",IF(AND(G8=3,H8=50%),"Successful",IF(AND(G8=4,H8 =65%),"Successful",IF(AND(G8=5,H8=75%),"Successf ul",IF(AND(G8=6,H8=90%),"Successful",IF(AND(g8=6 ,h8<=80%),"DoesNotMeet",IF(AND(G8=6,<=89%,"Incons istent",IF(AND(G8=6,H8<=114%"Successful",IF(AND(G 8=6,H8<=124%,"Excellent",IF(AND(G8=6,H8=125%,"E xceptional")))))))))))) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complex If/And Statement
I used the formula below with the data in cells A1:B5 and A7:B12. Now I am
getting a #Name? error. I'm not sure what I am doing incorrectly. I'm sure I need to reference the table somehow, but I'm not sure how I do that. =IF(G8<6,IF(H8VLOOKUP(G8,Table2,2),"Successful"," UNSUCCESSFUL"),VLOOKUP(H8,Table1,2)) "vezerid" wrote: I would use VLOOKUP here and with two tables: one for G8=6 and one for G8<6. Table 1: 0 DoesNotMeet 80% Inconsistent 90% Successful 115% Excellent 125% Exceptional Table 2: 0 0 1 30% 2 40% 3 50% 4 65% 5 75% Now your formula is compacted as follows, for the cases that G8 is < 6 and for when it is = 6. =IF(G8<6,IF(H8VLOOKUP (G8,Table2,2),"Successful","UNSUCCESSFUL"),VLOOKUP (H8,Table1,2)) Notes: I have added the "UNSUCCESSFUL" because your formula did not say what would happen if, e.g. G8=4 and H8<65%. Table1 and Table2 will be cell ranges like e.g. A1:B5 or C4:D9 HTH Kostis Vezerides On Nov 28, 7:36 pm, jonssmaster wrote: I'm stuck! I can't find a way to get this complex formula to work. First, where am I going wrong? And second, is there an easier way to write it? I'm guessing I have too many arguements. HELP! =IF(AND(G8=0,H8=0%),"Successful",IF(AND(G8=1,H8= 30%),"Successful",IF(AND(G8=2,H8=40%),"Successful ",IF(AND(G8=3,H8=50%),"Successful",IF(AND(G8=4,H8 =65%),"Successful",IF(AND(G8=5,H8=75%),"Successf ul",IF(AND(G8=6,H8=90%),"Successful",IF(AND(g8=6 ,h8<=80%),"DoesNotMeet",IF(AND(G8=6,<=89%,"Incons istent",IF(AND(G8=6,H8<=114%"Successful",IF(AND(G 8=6,H8<=124%,"Excellent",IF(AND(G8=6,H8=125%,"E xceptional")))))))))))) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complex If/And Statement
I suspect it is because you used my formula without any alteration.
The names Table1 and Table2 should not appear in your formula unless you defined a name for them. Replace Table2 in the formula with A1:B5 and Table1 with A7:B12 (or vice versa): =IF(G8<6,IF(H8VLOOKUP(G8,A1:B5,2),"Successful","U NSUCCESSFUL"),VLOOKUP (H8,A7:B12,2)) HTH Kostis On Nov 28, 10:49*pm, jonssmaster wrote: I used the formula below with the data in cells A1:B5 and A7:B12. Now I am getting a #Name? error. *I'm not sure what I am doing incorrectly. *I'm sure I need to reference the table somehow, but I'm not sure how I do that. * =IF(G8<6,IF(H8VLOOKUP(G8,Table2,2),"Successful"," UNSUCCESSFUL"),VLOOKUP(H8,Table1,2)) "vezerid" wrote: I would use VLOOKUP here and with two tables: one for G8=6 and one for G8<6. Table 1: 0 DoesNotMeet 80% Inconsistent 90% Successful 115% Excellent 125% Exceptional Table 2: 0 0 1 30% 2 40% 3 50% 4 65% 5 75% Now your formula is compacted as follows, for the cases that G8 is < 6 and for when it is = 6. =IF(G8<6,IF(H8VLOOKUP (G8,Table2,2),"Successful","UNSUCCESSFUL"),VLOOKUP (H8,Table1,2)) Notes: I have added the "UNSUCCESSFUL" because your formula did not say what would happen if, e.g. G8=4 and H8<65%. Table1 and Table2 will be cell ranges like e.g. A1:B5 or C4:D9 HTH Kostis Vezerides On Nov 28, 7:36 pm, jonssmaster wrote: I'm stuck! I can't find a way to get this complex formula to work. First, where am I going wrong? And second, is there an easier way to write it? *I'm guessing I have too many arguements. *HELP! =IF(AND(G8=0,H8=0%),"Successful",IF(AND(G8=1,H8= 30%),"Successful",IF(AND(G8=2,H8=40%),"Successful ",IF(AND(G8=3,H8=50%),"Successful",IF(AND(G8=4,H8 =65%),"Successful",IF(AND(G8=5,H8=75%),"Successf ul",IF(AND(G8=6,H8=90%),"Successful",IF(AND(g8=6 ,h8<=80%),"DoesNotMeet",IF(AND(G8=6,<=89%,"Incons istent",IF(AND(G8=6,H8<=114%"Successful",IF(AND(G 8=6,H8<=124%,"Excellent",IF(AND(G8=6,H8=125%,"E xceptional")))))))))))) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complex If/And Statement
You have a few missing parentheses; check that you have AND(G8...,H8...), In
some cases the closing ) is missing You have NOT exceeded the 7 level nesting You may wish to group all Successful conditions together as in: =IF(OR(AND(G8=0,H8=0%),AND(G8=1,H8=30%),AND(G8=2 ,H8=40%),AND(G8=3,H8=50%),AND(G8=4,H8=65%),AND( G8=5,H8=75%),AND(G8=6,H8=90%),AND(G8=6,H8<=114% )),"Successful",IF(AND(G8=6,H8<=80%),"DoesNotMeet ",IF(AND(G8=6,H8<=89%),"Inconsistent",IF(AND(G8= 6,H8<=124%),"Excellent",IF(AND(G8=6,H8=125%),"Ex ceptional"))))) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "jonssmaster" wrote in message ... I'm stuck! I can't find a way to get this complex formula to work. First, where am I going wrong? And second, is there an easier way to write it? I'm guessing I have too many arguements. HELP! =IF(AND(G8=0,H8=0%),"Successful",IF(AND(G8=1,H8= 30%),"Successful",IF(AND(G8=2,H8=40%),"Successful ",IF(AND(G8=3,H8=50%),"Successful",IF(AND(G8=4,H8 =65%),"Successful",IF(AND(G8=5,H8=75%),"Successf ul",IF(AND(G8=6,H8=90%),"Successful",IF(AND(g8=6 ,h8<=80%),"DoesNotMeet",IF(AND(G8=6,<=89%,"Incons istent",IF(AND(G8=6,H8<=114%"Successful",IF(AND(G 8=6,H8<=124%,"Excellent",IF(AND(G8=6,H8=125%,"E xceptional")))))))))))) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complex If/And Statement
Hi,
First there are a couple of logic errors: 1. =6 & =90% overlaps with =6 & <=114% and with =6 & <=124% and with =6 & =125% 2. There is a gap between =6 & <=124% and =6 & =125% - what happens to 7 & 124.4%? 3. You have nothing for 1 & <30%, 2 & <40%, .... Second, in 2003 and earlier you have a max of 7 nested levels, in 2007 that is up to 64. You could set up a range like this 0 0 1 1 30% 1 2 40% 1 3 50% 1 4 65% 1 5 75% 1 6 90% 1 6 0 0.8 2 6 0.8 0.89 3 6 0.89 1.14 1 6 1.14 1.24 4 6 1.24 111 5 And use the following formula =CHOOSE(SUMPRODUCT(--(A1:A7=A15),--(B15=B1:B7),D1:D7)+SUMPRODUCT(--(A15A8:A12),--(B15B8:B12),--(B15<=C8:C12),D8:D12),"Successful","Does not Meet","Inconsistant","Excellent","Exceptional") If this helps, please click the Yest button Cheers, Shane Devenshire "jonssmaster" wrote: I'm stuck! I can't find a way to get this complex formula to work. First, where am I going wrong? And second, is there an easier way to write it? I'm guessing I have too many arguements. HELP! =IF(AND(G8=0,H8=0%),"Successful",IF(AND(G8=1,H8= 30%),"Successful",IF(AND(G8=2,H8=40%),"Successful ",IF(AND(G8=3,H8=50%),"Successful",IF(AND(G8=4,H8 =65%),"Successful",IF(AND(G8=5,H8=75%),"Successf ul",IF(AND(G8=6,H8=90%),"Successful",IF(AND(g8=6 ,h8<=80%),"DoesNotMeet",IF(AND(G8=6,<=89%,"Incons istent",IF(AND(G8=6,H8<=114%"Successful",IF(AND(G 8=6,H8<=124%,"Excellent",IF(AND(G8=6,H8=125%,"E xceptional")))))))))))) |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Complex If/And Statement
Thanks to everyone for the great suggestions. All options will work, so I'll
have to choose the best one. Thanks again! "Shane Devenshire" wrote: Hi, First there are a couple of logic errors: 1. =6 & =90% overlaps with =6 & <=114% and with =6 & <=124% and with =6 & =125% 2. There is a gap between =6 & <=124% and =6 & =125% - what happens to 7 & 124.4%? 3. You have nothing for 1 & <30%, 2 & <40%, .... Second, in 2003 and earlier you have a max of 7 nested levels, in 2007 that is up to 64. You could set up a range like this 0 0 1 1 30% 1 2 40% 1 3 50% 1 4 65% 1 5 75% 1 6 90% 1 6 0 0.8 2 6 0.8 0.89 3 6 0.89 1.14 1 6 1.14 1.24 4 6 1.24 111 5 And use the following formula =CHOOSE(SUMPRODUCT(--(A1:A7=A15),--(B15=B1:B7),D1:D7)+SUMPRODUCT(--(A15A8:A12),--(B15B8:B12),--(B15<=C8:C12),D8:D12),"Successful","Does not Meet","Inconsistant","Excellent","Exceptional") If this helps, please click the Yest button Cheers, Shane Devenshire "jonssmaster" wrote: I'm stuck! I can't find a way to get this complex formula to work. First, where am I going wrong? And second, is there an easier way to write it? I'm guessing I have too many arguements. HELP! =IF(AND(G8=0,H8=0%),"Successful",IF(AND(G8=1,H8= 30%),"Successful",IF(AND(G8=2,H8=40%),"Successful ",IF(AND(G8=3,H8=50%),"Successful",IF(AND(G8=4,H8 =65%),"Successful",IF(AND(G8=5,H8=75%),"Successf ul",IF(AND(G8=6,H8=90%),"Successful",IF(AND(g8=6 ,h8<=80%),"DoesNotMeet",IF(AND(G8=6,<=89%,"Incons istent",IF(AND(G8=6,H8<=114%"Successful",IF(AND(G 8=6,H8<=124%,"Excellent",IF(AND(G8=6,H8=125%,"E xceptional")))))))))))) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Complex IF STATEMENT | Excel Discussion (Misc queries) | |||
| Help with complex If statement | Excel Worksheet Functions | |||
| Another Extremely complex IF statement | Excel Worksheet Functions | |||
| Yet another extremely complex IF statement | Excel Worksheet Functions | |||
| complex IF(OR(...AND())) statement | Excel Discussion (Misc queries) | |||
Hybrid Mode
