IF(AND) error message

[Guest]

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 ??

 

[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.

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Gord Dibben]

The limit on nested functions like IF is seven to a formula.

Workarounds using named ranges examples available at Chip Pearson's site.

http://www.cpearson.com/excel/nested.htm


Gord Dibben MS Excel MVP