Complex If/And Statement

[jonssmaster]

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%),"Successful",IF(AND(G8=6,H8>=90%),"Successful",IF(AND(g8>=6,h8<=80%),"DoesNotMeet",IF(AND(G8>=6,<=89%,"Inconsistent",IF(AND(G8>=6,H8<=114%"Successful",IF(AND(G8>=6,H8<=124%,"Excellent",IF(AND(G8>=6,H8>=125%,"Exceptional"))))))))))))

 

[Tom Hutchins]

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%),"Exceptional","???"))))))

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

 

[vezerid]

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(H8>VLOOKUP
(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 C49

HTH
Kostis Vezerides

 

[Bernard Liengme]

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%),"Exceptional")))))

best wishes

 

[jonssmaster]

Thank you so much. That worked Great.

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%),"Exceptional","???"))))))

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]

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(H8>VLOOKUP(G8,Table2,2),"Successful","UNSUCCESSFUL"),VLOOKUP(H8,Table1,2))

 

[Shane Devenshire]

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),D17)+SUMPRODUCT(--(A15>A8:A12),--(B15>B8:B12),--(B15<=C8:C12),D812),"Successful","Does
not Meet","Inconsistant","Excellent","Exceptional")

If this helps, please click the Yest button

Cheers,
Shane Devenshire

 

[vezerid]

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(H8>VLOOKUP(G8,A1:B5,2),"Successful","UNSUCCESSFUL"),VLOOKUP
(H8,A7:B12,2))

HTH
Kostis

 

[jonssmaster]

Thanks to everyone for the great suggestions. All options will work, so I'll
have to choose the best one. Thanks again!