IF(AND) error message

M

mr_concrete

I am getting an error message on the 8th time I use IF(AND) in the same
formula. Is there a maximum number of times you can use this combonation in a
singular formula?? If so, how do I end the first part of the formula but
start a second part ??
 
J

JE McGimpsey

You've found the nesting limit (7, for XL versions other than XL2007).

in general, using lookups or math formulae are more appropriate when
nesting gets that deep.

You can find this type of information on XL's limits in the
"Specifications" Help topic.
 
D

Dave F

Excel has a limit of 7 nested functions. Post the formula you would like to
use and someone can come up with an alternative that avoids this limit.

Dave
 
M

mr_concrete

Here is the formula I started, I need to be able to go quite a bit more like
this:

=IF(AND(D3>4.1,E3=100),40,IF(AND(D3>4.1,E3>=95),30,IF(AND(D3>4.1,E3>=90),25,IF(AND(D3>4.1,E3>=85),20,IF(AND(D3>4.1,E3>=80),19,IF(AND(D3>4,E3=100),30,IF(AND(D3>4,E3>=95),25,IF(AND(D3>4,E3>=90),19,IF(AND(D3>4,E3>=85),18,IF(AND(D3>4,E3>=80),17,0))))))))))

Please advise.
 
D

driller

maybe something like this

=IF(D3>4.1,LOOKUP(E3,{80,85,90,95,100},{19,20,25,30,40}),IF(D3>4,LOOKUP(E3,{80,85,90,95,100},{17,18,19,25,30}),0))

regards
 
Top