more levels of nesting than allowed in a formula

Pursuit2010 · Oct 20, 2010

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

GlennUK · Oct 20, 2010

You do not appear to be closing your AND functions. Shouldn't this be something like:

Code:

=IF(OR(AND($K8=0,($L8>=13)*($L8<=19),($N8>=25)*($N8<=30.9)),AND($K8=1,($L8>=13)*($L8<=19),($N8>=35)*($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)*($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))),"1","0")

ukmikeb · Oct 20, 2010

Hi

If you breakdown your formula as follows -

Code:

[FONT=Verdana][COLOR=black][COLOR=black][FONT=Verdana]IF(<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]OR(<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]AND($K8=0,($L8>=13)*($L8<=19),($N8>=25.0)*($N8<=30.9),<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]AND($K8=0,($L8>=20)*($L8<=29),($N8>=23.6)*($N8<=28.9),<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]AND($K8=0,($L8>=30)*($L8<=39),($N8>=22.8)*($N8<=26.9),<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]AND($K8=0,($L8>=40)*($L8<=49),($N8>=21.0)*($N8<=24.4),<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]AND($K8=0,($L8>=50)*($L8<=59),($N8>=20.2)*($N8<=22.7),<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]AND($K8=0,($L8>=60),($N8>=17.5)*($N8<=20.1),<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]AND($K8=1,($L8>=13)*($L8<=19),($N8>=35.0)*($N8<38.3),<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]AND($K8=1,($L8>=20)*($L8<=29),($N8>=33)*($N8<=36.4),<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]AND($K8=1,($L8>=30)*($L8<=39),($N8>=31.5)*($N8<=35.4),<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]AND($K8=1,($L8>=13)*($L8<=49),($N8>=30.2)*($N8<=33.5),<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]AND($K8=1,($L8>=13)*($L8<=59),($N8>=26.1)*($N8<=30.9),<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]AND($K8=1,($L8>=60),($N8>=20.5)*($N8<=26.0))<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana],"1","0")[/FONT][/COLOR]
[/COLOR][/FONT]

and use Glenn's suggestion as well you should be able to reduce the levels of nesting dramatically.

And you may have an incorrect section in K8=1 after L8 <= 39.

hth

Mike

kkrishna · Jan 17, 2011

Hi,
can someone shorten the formula? as i'm getting the error as "The specified formula can not be entered because it uses more levels of nesting than are allowed in the current file format"

=IF(AND(K18=4,K20=4,K22=4),"A",IF(AND(K18=4,K20=4,K22=3),"A",IF(AND(K18=4,K20=3,K22=4),"A",IF(AND(K18=4,K20=3,K22=3),"B",IF(AND(K18=4,K20=4,K22=2),"B",IF(AND(K18=3,K20=3,K22=3), "C",IF(AND(K18=3,K20=3,K22=2), "C",IF(AND(K18=3,K20=2,K22=2), "D",IF(AND(K18<=2,K20<=2,K22<=2),"Fail","")))))))))

Thanks in advance

Peter_SSs · Jan 17, 2011

1. There seems to be quite a few combinations not catered for. Here's just a few for example.

<TABLE style="WIDTH: 126pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=168><COLGROUP><COL style="WIDTH: 42pt" span=3 width=56><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 42pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17 width=56>K18</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=56>K20</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=56>K22</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=17 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=17 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=17 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=17 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>4</TD></TR></TBODY></TABLE>

2. Is there any logic (in words) to how the letters are assigned?

jasonb75 · Jan 17, 2011

Wild guess based on what I'm seeing in your formula,

=LOOKUP(SUM(K18,K20,K22),{0,7,8,10,11},{"Fail","D","C","B","A"})

danjuma · May 15, 2011

Getting the message: "The specified formula can not be entered because it uses more levels of nesting than are allowed in the current file format"

while trying to enter the formula below:

=if(I4="","",if(and(I4="B",L4="A"),1,if(and(I4="B",L4="B"),2,if(and(I4="B",L4="C"),3,if(and(I4="B",L4="D"),4,if(and(I4="B",L4="E"),5,if(and(I4="BC",L4="A"),6,if(and(I4="BC",L4="B"),7,
if(and(I4="BC",L4="C"),8,if(and(I4="BC",L4="D"),9,if(and(I4="BC",L4="E"),10,if(and(I4="VH",L4="A"),11,if(and(I4="VH",L4="B"),12,if(and(I4="VH",L4="C"),13,
if(and(I4="VH",L4="D"),14,15)))))))))))))))

Can't see what the problem is. Would be grateful if somebody could assist please?

Many thanks.

Peter_SSs · May 15, 2011

If you are using Excel 2003- you can only have 7 levels of nesting. Rearranging your formula as below shows that you have many more levels than that.

=IF(I4="","",
IF(AND(I4="B",L4="A"),1,
IF(AND(I4="B",L4="B"),2,
IF(AND(I4="B",L4="C"),3,
IF(AND(I4="B",L4="D"),4,
IF(AND(I4="B",L4="E"),5,
IF(AND(I4="BC",L4="A"),6,
IF(AND(I4="BC",L4="B"),7,
IF(AND(I4="BC",L4="C"),8,
IF(AND(I4="BC",L4="D"),9,
IF(AND(I4="BC",L4="E"),10,
IF(AND(I4="VH",L4="A"),11,
IF(AND(I4="VH",L4="B"),12,
IF(AND(I4="VH",L4="C"),13,
IF(AND(I4="VH",L4="D"),14,15)))))))))))))))

If you need more than 7 levels there is usually a better way to approach the problem. See if this does what you want.

=IF(I4="","",LOOKUP(I4,{"B","BC","VH"},{1,6,11})+CODE(L4)-65)

danjuma · May 15, 2011

If you need more than 7 levels there is usually a better way to approach the problem. See if this does what you want.

=IF(I4="","",LOOKUP(I4,{"B","BC","VH"},{1,6,11})+CODE(L4)-65)

Your formula above worked fantastically. Sir, you are the man! I am most grateful!

danjuma · May 15, 2011

Sir, I need your help once more please. trying to enter this one below as well, but can't really work out how to do it your suggested way - using Lookup and the code thing. My knowledge of excel is still primitive

Many thanks.

=if(AL4="","",
if(AL4=1,$Bc$18,
if(AL4=2,$Bi$18,
if(AL4=3,$Bc$39,
if(AL4=4,$Bi$39,
if(AL4=5,$BC$60,
if(AL4=6,$BC$20,
if(AL4=7,$BI$20,
if(AL4=8,$BC$41,
if(AL4=9,$BI$41,
if(AL4=10,$BC$62,
if(AL4=11,2*$BC$19,
if(AL4=12,2*$BI$19,
if(AL4=13,2*$BC$40,
if(AL4=14,2*$BI$40,
if(AL4=15,2*$BC$61,""))))))))))))))))

more levels of nesting than allowed in a formula

Pursuit2010

New Member

Excel Facts

GlennUK

Well-known Member

ukmikeb

Well-known Member

kkrishna

New Member

Peter_SSs

MrExcel MVP, Moderator

jasonb75

Well-known Member

danjuma

Active Member

Peter_SSs

MrExcel MVP, Moderator

danjuma

Active Member

danjuma

Active Member

Similar threads

Forum statistics

Share this page

more levels of nesting than allowed in a formula

New Member

Excel Facts

Well-known Member

Well-known Member

New Member

MrExcel MVP, Moderator

Well-known Member

Active Member

MrExcel MVP, Moderator

Active Member

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock