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

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.


In article ,
mr_concrete wrote:

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

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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"mr_concrete" wrote:

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

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

=IF(AND(D34.1,E3=100),40,IF(AND(D34.1,E3=95),30 ,IF(AND(D34.1,E3=90),25,IF(AND(D34.1,E3=85),20 ,IF(AND(D34.1,E3=80),19,IF(AND(D34,E3=100),30,I F(AND(D34,E3=95),25,IF(AND(D34,E3=90),19,IF(AN D(D34,E3=85),18,IF(AND(D34,E3=80),17,0)))))))) ))

Please advise.

"Dave F" wrote:

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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"mr_concrete" wrote:

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

maybe something like this

=IF(D34.1,LOOKUP(E3,{80,85,90,95,100},{19,20,25,3 0,40}),IF(D34,LOOKUP(E3,{80,85,90,95,100},{17,18, 19,25,30}),0))

regards
--
*****
birds of the same feather flock together..



"mr_concrete" wrote:

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

=IF(AND(D34.1,E3=100),40,IF(AND(D34.1,E3=95),30 ,IF(AND(D34.1,E3=90),25,IF(AND(D34.1,E3=85),20 ,IF(AND(D34.1,E3=80),19,IF(AND(D34,E3=100),30,I F(AND(D34,E3=95),25,IF(AND(D34,E3=90),19,IF(AN D(D34,E3=85),18,IF(AND(D34,E3=80),17,0)))))))) ))

Please advise.

"Dave F" wrote:

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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"mr_concrete" wrote:

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

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

On Tue, 6 Feb 2007 10:21:01 -0800, mr_concrete
wrote:

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