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

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

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

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

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

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

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

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

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