more levels of nesting than allowed in a formula

[Pursuit2010]

<TABLE style="WIDTH: 581pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=773><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1938" width=53><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><COL style="WIDTH: 100pt; mso-width-source: userset; mso-width-alt: 4864" width=133><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 581pt; HEIGHT: 153pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2481960 class=xl69 height=204 rowSpan=12 width=773 colSpan=10>IF(OR(AND($K8=0,($L8>=13)*($L8<=19),($N8>=25.0)*($N8<=30.9),AND($K8=1,($L8>=13)*($L8<=19),($N8>=35.0)*($N8<38.3),AND($K8=0,($L8>=20)*($L8<=29),($N8>=23.6)*($N8<=28.9),AND($K8=1,($L8>=20)*($L8<=29),($N8>=33)*($N8<=36.4),AND($K8=0,($L8>=30)*($L8<=39),($N8>=22.8)*($N8<=26.9),AND($K8=1,($L8>=30)*($L8<=39),($N8>=31.5)*($N8<=35.4),AND($K8=0,($L8>=40)*($L8<=49),($N8>=21.0)*($N8<=24.4),AND($K8=1,($L8>=13)*($L8<=49),($N8>=30.2)*($N8<=33.5),AND($K8=0,($L8>=50)*($L8<=59),($N8>=20.2)*($N8<=22.7),AND($K8=1,($L8>=13)*($L8<=59),($N8>=26.1)*($N8<=30.9),AND($K8=0,($L8>=60),($N8>=17.5)*($N8<=20.1),AND($K8=1,($L8>=60),($N8>=20.5)*($N8<=26.0)),"1","0")

</TD></TR>












Below is the formula I need to use but it tells me there are more levels of nesting than allowed.
What do I do to make it work?
<TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR><TR style="HEIGHT: 12.75pt" height=17></TR></TBODY></TABLE>

 

[SG 2011]

I have a very large Nested IF formula that I am trying to use. In brief, it checks the value of 3 different cells (AW7, AO7 and AU7) and accordingly SUMS different conditions.

=IF(AND(AW7=4,(OR(AND(AO7>=8,AU7>=0)))),SUM(B7,LARGE(C7:R7,{1,2,3,4,5,6,7,8})),IF(OR(AND(AO7>=7,AU7>=2)),SUM(B7,LARGE(C7:R7,{1,2,3,4,5,6,7}),LARGE(S7:AB7,{1,2})),IF(OR(AND(AO7>=6,AU7>=4)),SUM(B7,LARGE(C7:R7,{1,2,3,4,5,6}),LARGE(S7:AB7,{1,2,3,4})),IF(AND(AW7=3,(OR(AND(AO7>=6,AU7>=0)))),SUM(B7,LARGE(C7:R7,{1,2,3,4,5,6})),IF(OR(AND(AO7>=5,AU7>=2)),SUM(B7,LARGE(C7:R7,{1,2,3,4,5}),LARGE(S7:AB7,{1,2})),IF(OR(AND(AO7>=4,AU7>=4)),SUM(B7,LARGE(C7:R7,{1,2,3,4}),LARGE(S7:AB7,{1,2,3,4})),IF(AND(AW7=2,(OR(AND(AO7>=3,AU7>=1)))),SUM(B7,LARGE(C7:R7,{1,2,3}),LARGE(S7:AB7,{1})),IF(OR(AND(AO7>=2,AU7>=3)),SUM(B7,LARGE(C7:R7,{1,2}),LARGE(S7:AB7,{1,2,3})),IF(OR(AND(AO7>=1,AU7>=5)),SUM(B7,LARGE(C7:R7,{1}),LARGE(S7:AB7,{1,2,3,4,5})),IF(AND(AW7=1,(OR(AND(AO7>=1,AU7>=1)))),SUM(B7,LARGE(C7:R7,{1}),LARGE(S7:AB7,{1})),IF(OR(AND(AO7>=0,AU7>=3)),SUM(B7,LARGE(S7:AB7,{1,2,3})))))))))))))

It works in Excel 2007 (due to it's ability to handle more than 7 levels of nesting), however when I apply it to an 2003 .xls workbook format, I get the usual "the specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format" error message.

Since some of the machines that it is to be used are operating on the old 2003 .xls format, is there a way I can simplify it, so that it would work.

Could the CHOOSE function possibly help?


Many thanks,

Shyam

 

[jasonb75]

Hi Shyam,

I have a feeling that your formula is not doing what you think it is, could you explain the criteria that think this part should be testing, I'm trying to get an understanding of why you're using the OR function here.

AND(AW7=4,(OR(AND(AO7>=8,AU7>=0))))

 

[shg]

I agree with Jason. Taking out the redundant logical functions, the formula reduces to

=IF(AND(AO7>=8, AW7=4,  AU7>=0), B7 + LARGE(C7:R7, {1,2,3,4,5,6,7,8}),
 IF(AND(AO7>=7,         AU7>=2), B7 + LARGE(C7:R7, {1,2,3,4,5,6,7}) + LARGE(S7:AB7, {1,2}),
 IF(AND(AO7>=6,         AU7>=4), B7 + LARGE(C7:R7, {1,2,3,4,5,6})   + LARGE(S7:AB7, {1,2,3,4}),
 IF(AND(AO7>=6, AW7=3,  AU7>=0), B7 + LARGE(C7:R7, {1,2,3,4,5,6}),
 IF(AND(AO7>=5,         AU7>=2), B7 + LARGE(C7:R7, {1,2,3,4,5})     + LARGE(S7:AB7, {1,2}),
 IF(AND(AO7>=4,         AU7>=4), B7 + LARGE(C7:R7, {1,2,3,4})       + LARGE(S7:AB7, {1,2,3,4}),
 IF(AND(AO7>=3, AW7=2,  AU7>=1), B7 + LARGE(C7:R7, {1,2,3})         + LARGE(S7:AB7, {1}),
 IF(AND(AO7>=2,         AU7>=3), B7 + LARGE(C7:R7, {1,2})           + LARGE(S7:AB7, {1,2,3}),
 IF(AND(AO7>=1,         AU7>=5), B7 + LARGE(C7:R7, {1})             + LARGE(S7:AB7, {1,2,3,4,5}),
 IF(AND(AO7>=1, AW7=1,  AU7>=1), B7 + LARGE(C7:R7, {1})             + LARGE(S7:AB7, {1}),
 IF(AND(AO7>=0,         AU7>=3), B7                                 + LARGE(S7:AB7, {1,2,3}))))))))))))

 

[SG 2011]

Hello Jason,

Many thanks for your reply.

I've used the OR function here to say:
IF AW7=4 AND... AO7>=8 and AU7>=0 then SUM x,y,z.
OR, IF AW7=4 AND... AO7>=7 and AU7>=2 then SUM x,y,z.
OR, IF AW7=4 AND...AO7>=6,AU7>=4 then SUM x,y,z.

The formula does seem to work, but I just noticed that I also need the formula to return the highest SUM value of the 3 conditions.

Many thanks for your interest,

Shyam

Hi Shyam,

I have a feeling that your formula is not doing what you think it is, could you explain the criteria that think this part should be testing, I'm trying to get an understanding of why you're using the OR function here.

AND(AW7=4,(OR(AND(AO7>=8,AU7>=0))))

 

[SG 2011]

Hi there,

Interesting contribution...

I just implemented your formula and the totals are not coming out correct.

For example: When AW7=3, AO7=5, AU7=2
The value of B7=1
The collective value of C7:R7=5
The collective value of S7:AB7=3

The answer should = 9, but your formula shows 3.5?

Any thoughts?

Shyam

I agree with Jason. Taking out the redundant logical functions, the formula reduces to

=IF(AND(AO7>=8, AW7=4,  AU7>=0), B7 + LARGE(C7:R7, {1,2,3,4,5,6,7,8}),
 IF(AND(AO7>=7,         AU7>=2), B7 + LARGE(C7:R7, {1,2,3,4,5,6,7}) + LARGE(S7:AB7, {1,2}),
 IF(AND(AO7>=6,         AU7>=4), B7 + LARGE(C7:R7, {1,2,3,4,5,6})   + LARGE(S7:AB7, {1,2,3,4}),
 IF(AND(AO7>=6, AW7=3,  AU7>=0), B7 + LARGE(C7:R7, {1,2,3,4,5,6}),
 IF(AND(AO7>=5,         AU7>=2), B7 + LARGE(C7:R7, {1,2,3,4,5})     + LARGE(S7:AB7, {1,2}),
 IF(AND(AO7>=4,         AU7>=4), B7 + LARGE(C7:R7, {1,2,3,4})       + LARGE(S7:AB7, {1,2,3,4}),
 IF(AND(AO7>=3, AW7=2,  AU7>=1), B7 + LARGE(C7:R7, {1,2,3})         + LARGE(S7:AB7, {1}),
 IF(AND(AO7>=2,         AU7>=3), B7 + LARGE(C7:R7, {1,2})           + LARGE(S7:AB7, {1,2,3}),
 IF(AND(AO7>=1,         AU7>=5), B7 + LARGE(C7:R7, {1})             + LARGE(S7:AB7, {1,2,3,4,5}),
 IF(AND(AO7>=1, AW7=1,  AU7>=1), B7 + LARGE(C7:R7, {1})             + LARGE(S7:AB7, {1}),
 IF(AND(AO7>=0,         AU7>=3), B7                                 + LARGE(S7:AB7, {1,2,3}))))))))))))

 

[shg]

Try using the formula auditing toolbar, and watch the formula evaluate.

 

[jasonb75]

It looks ugly but I think it's right, and within the nesting limit.

=CHOOSE(AW7,
MAX(IF(AND(AO7>=1,AU7>=1),SUM(B7,MAX(C7:R7),MAX(S7:AB7))),IF(AND(AO7>=0,AU7>=3),SUM(B7,LARGE(S7:AB7,{1,2,3})))),
MAX(IF(AND(AO7>=3,AU7>=1),SUM(B7,LARGE(C7:R7,{1,2,3}),MAX(S7:AB7))),IF(AND(AO7>=2,AU7>=3),SUM(B7,LARGE(C7:R7,{1,2}),LARGE(S7:AB7,{1,2,3}))),IF(AND(AO7>=1,AU7>=5),SUM(B7,MAX(C7:R7),LARGE(S7:AB7,{1,2,3,4,5})))),
MAX(IF(AND(AO7>=6,AU7>=0),SUM(B7,LARGE(C7:R7,{1,2,3,4,5,6}))),IF(AND(AO7>=5,AU7>=2),SUM(B7,LARGE(C7:R7,{1,2,3,4,5}),LARGE(S7:AB7,{1,2}))),IF(AND(AO7>=4,AU7>=4),SUM(B7,LARGE(C7:R7,{1,2,3,4}),LARGE(S7:AB7,{1,2,3,4})))),
MAX(IF(AND(AO7>=8,AU7>=0),SUM(B7,LARGE(C7:R7,{1,2,3,4,5,6,7,8}))),IF(AND(AO7>=7,AU7>=2),SUM(B7,LARGE(C7:R7,{1,2,3,4,5,6,7}),LARGE(S7:AB7,{1,2}))),IF(AND(AO7>=6,AU7>=4),SUM(B7,LARGE(C7:R7,{1,2,3,4,5,6}),LARGE(S7:AB7,{1,2,3,4})))))

 

[SG 2011]

Hi Jason,

I think you might just have it here!

It's much different to what I thought it would look like, but on initial testing, it seems to work beautifully!

I'll have to spend some time now figuring out the logic behind it and how it all works...

Thank you so much - you've really been a great help!!


Kindest regards,

Shyam

It looks ugly but I think it's right, and within the nesting limit.

=CHOOSE(AW7,
MAX(IF(AND(AO7>=1,AU7>=1),SUM(B7,MAX(C7:R7),MAX(S7:AB7))),IF(AND(AO7>=0,AU7>=3),SUM(B7,LARGE(S7:AB7,{1,2,3})))),
MAX(IF(AND(AO7>=3,AU7>=1),SUM(B7,LARGE(C7:R7,{1,2,3}),MAX(S7:AB7))),IF(AND(AO7>=2,AU7>=3),SUM(B7,LARGE(C7:R7,{1,2}),LARGE(S7:AB7,{1,2,3}))),IF(AND(AO7>=1,AU7>=5),SUM(B7,MAX(C7:R7),LARGE(S7:AB7,{1,2,3,4,5})))),
MAX(IF(AND(AO7>=6,AU7>=0),SUM(B7,LARGE(C7:R7,{1,2,3,4,5,6}))),IF(AND(AO7>=5,AU7>=2),SUM(B7,LARGE(C7:R7,{1,2,3,4,5}),LARGE(S7:AB7,{1,2}))),IF(AND(AO7>=4,AU7>=4),SUM(B7,LARGE(C7:R7,{1,2,3,4}),LARGE(S7:AB7,{1,2,3,4})))),
MAX(IF(AND(AO7>=8,AU7>=0),SUM(B7,LARGE(C7:R7,{1,2,3,4,5,6,7,8}))),IF(AND(AO7>=7,AU7>=2),SUM(B7,LARGE(C7:R7,{1,2,3,4,5,6,7}),LARGE(S7:AB7,{1,2}))),IF(AND(AO7>=6,AU7>=4),SUM(B7,LARGE(C7:R7,{1,2,3,4,5,6}),LARGE(S7:AB7,{1,2,3,4})))))

 

[danjuma]

Hello,

Trying to enter the following formula in a cell, but coming up with error that I have more nested IFs allowed by excel. Would be very grateful for someone's assistance please. Many thanks.

=if(A8="","",if(and(AI8="",AJ8="",AK8="",AL8="",AM8="",AN8=""),0,if(and(AI8<>"",AJ8="",AK8="",AL8="",AM8="",AN8=""),1,if(and(AI8<>"",AJ8<>"",AK8="",AL8="",AM8="",AN8=""),2,if(and(AI8<>"",AJ8<>"",AK8<>"",AL8="",AM8="",AN8=""),3,if(and(AI8<>"",AJ8<>"",AK8<>"",AL8<>"",AM8="",AN8=""),4,if(and(AI8<>"",AJ8<>"",AK8<>"",AL8<>"",AM8<>"",AN8=""),5,if(and(AI8<>"",AJ8<>"",AK8<>"",AL8<>"",AM8<>"",AN8<>""),6,""))))))))

Formula broken down line by line:

=if(A8="","",
if(and(AI8="",AJ8="",AK8="",AL8="",AM8="",AN8=""),0,
if(and(AI8<>"",AJ8="",AK8="",AL8="",AM8="",AN8=""),1,
if(and(AI8<>"",AJ8<>"",AK8="",AL8="",AM8="",AN8=""),2,
if(and(AI8<>"",AJ8<>"",AK8<>"",AL8="",AM8="",AN8=""),3,
if(and(AI8<>"",AJ8<>"",AK8<>"",AL8<>"",AM8="",AN8=""),4,
if(and(AI8<>"",AJ8<>"",AK8<>"",AL8<>"",AM8<>"",AN8=""),5,
if(and(AI8<>"",AJ8<>"",AK8<>"",AL8<>"",AM8<>"",AN8<>""),6,""))))))))

 

[Peter_SSs]

I'm guessing that columns AI:AN are gradually filled from left to right. If so and those columns

- are blank or contain numbers, then try
=IF(A8="","",COUNT(AI8:AN8))

- are blank or may contain text (no formulas) then try
=IF(A8="","",COUNTA(AI8:AN8))

- are blank or may contain text (with formulas) then try
=IF(A8="","",6-COUNTBLANK(AI8:AN8))

If columns AI:AN are not always filled from left to right, then please confirm that if there are any 'gaps' in those columns the result should be ""
Also tell us if there are formulas in AI:AN or whether the values are manually entered.
Is it always numbers in AI:AN, or text or possibly both?