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>

 

[Kabouter]

Hi All PLEASE assist
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"

=IF(F5<151,"1",IF(F5<201,"2",IF(F5<276,"3",IF(F5<351,"4",IF(F5<401,"5",IF(F5<451,"6",IF(F5<476,"7",IF(F5<501,"8",IF(F5<526,"9",IF(F5<750,"10"))))))))))

 

[shg]

=lookup(f5, {-9e307,151,201,276,351,401,451,476,501,526,750}, {1,2,3,4,5,6,7,8,9,10,"?"})

 

[Kabouter]

Thank YOU!!!

 

[AshChu]

Hi all,

I have encounter the message "more lvl of nesting than allowed in a formula"
=IF(AND(E3="L12",F3="0"),"0013",IF(AND(E3="L12",F3="2"),"13",IF(AND(E3="L36",F3="2"),"12",IF(AND(E3="L40",F3="2"),"2",IF(AND(E3="L40",F3="0"),"0002",IF(AND(E3="L48",F3="0"),"0021",IF(AND(E3="L48",F3="2"),"21","0")))))))
This is the max i level i can have. will like to add more....
Is there any way to simplify this?

 

[Peter_SSs]

Is there any way to simplify this?

Welcome to the MrExcel board!

One way would be to create a lookup table like I have below. Column J contains the E3|F3 combinations you are interested in. Column K is formatted as Text and then has the corresponding results entered in it. Then the VLOOKUP formula shown in G3 should return the appropriate results.

Excel Workbook
	E	F	G	H	I	J	K
1						E3|F3	Result
2						L12|0	0013
3	L40	0	0002			L12|2	13
4						L36|2	12
5						L40|2	2
6						L40|0	0002
7						L48|0	0021
8						L48|2	21
9
AshChu

 

[AshChu]

Hi Peter

Its working fine now. Learn something new.

Thank you so much

 

[Peter_SSs]

Hi Peter

Its working fine now. Learn something new.

Thank you so much

Glad it helped. Thanks for letting us know.

 

[msusig]

I too need some help with this. Below is my formula. This is the most I could input before it throws the error.
Thanks in advance!!

=IF(AND(B8>=24.5,B8<=28.49),8,IF(AND(B8>=28.5,B8<=32.49),10,IF(AND(B8>=32.5,B8<=36.49),12,IF(AND(B8>=36.5,B8<=40.49),14,IF(AND(B8>=40.5,B8<=44.49),16,IF(AND(B8>=44.5,B8<=48.49),18,IF(AND(B8>=48.5,B8<=52.49),20,"NO")))))))

 

[Eric W]

Same type of thing as in post #72. You're looking to find the range where B4 lies:

=LOOKUP(B4,{0,24.5,28.5,32.5,36.5,40.5,44.5,48.5},{"NO",8,10,12,14,16,18,20})

This is easy enough to put in a table in your spread sheet too. If the values in the first array are put in J1:J8 and the second array is in K1:K8, then the formula is:

=LOOKUP(B4,J1:K8)

This might be easier for long-term maintenance.

 

[msusig]

Thank you so much Eric. Yeah, I was going through this thread and really trying to figure out the lookup feature. In the last 2 hours I've learned quite a bit

Thanks again!