If function.. Error - the specified formula cannot be entered because it used more levels of nesting....

[Sandeep Singh]

Hi All,

Hope Everyone is Doing Great !!!

Data on which I am applying formula.

[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl63, width: 64"]CA[/TD]
[TD="class: xl64, width: 64"]1-99[/TD]
[TD="class: xl63, width: 64"]DH[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]100-199[/TD]
[TD="class: xl63"]NH[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]200-299[/TD]
[TD="class: xl63"]ME[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]500-599[/TD]
[TD="class: xl63"]IJ[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]600-699[/TD]
[TD="class: xl63"]UY[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]800-899[/TD]
[TD="class: xl63"]PL[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]900-999[/TD]
[TD="class: xl63"]UJ[/TD]
[/TR]
[TR]
[TD="class: xl63"]AZ[/TD]
[TD="class: xl64"]1-64[/TD]
[TD="class: xl63"]MN[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]64-389[/TD]
[TD="class: xl63"]OP[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]390-550[/TD]
[TD="class: xl63"]LA[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]551-799[/TD]
[TD="class: xl63"]SF[/TD]
[/TR]
[TR]
[TD="class: xl63"]NH[/TD]
[TD="class: xl63"]54-95[/TD]
[TD="class: xl63"]LO[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]96-155[/TD]
[TD="class: xl63"]ER[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]156-680[/TD]
[TD="class: xl63"]CO[/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"]681-999[/TD]
[TD="class: xl63"]GH[/TD]
[/TR]
</tbody>[/TABLE]



My If formula is ..

IF(D3="CA",IF(AND(E3>=1,E3<=99),C3,IF(AND(E3>=100,E3<=199),C4,IF(AND(E3>=200,E3<=299),C5,IF(AND(E3>=500,E3<=599),
C6,IF(AND(E3>=600,E3<=699),C7,IF(AND(E3>=800<=899),C8,IF(AND(E3>=900,E3<=999),C9,"Not InRange"))))))),IF(D3="AZ",IF(AND(E3>=1,E3<=64),C10,IF(AND(E3>=64,E3<=389),C11,IF(AND(E3>=390<=550),C12,IF(AND(E3>=551,E3<=799),C13,"Not In Range"))))))

I have many more if conditions to add on... but i am getting error "the specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format".

Then I have Google it to see some other formula where i can i reduce if's.

I tried this "IF(D3="CA",LOOKUP(E3,{"1-99","100-199","200-299"},{"DH","NH","ME"})) its not working.

I need some help on this... Thanks for the help in advance.

 

[yesterdays]

[TABLE="class: grid, width: 521, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]CA[/TD]
[TD="align: center"]1-99[/TD]
[TD="align: center"]DH[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]CA[/TD]
[TD="align: center"][/TD]
[TD="align: center"]DH[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]CA[/TD]
[TD="align: center"]100-199[/TD]
[TD="align: center"]NH[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]AZ[/TD]
[TD="align: center"][/TD]
[TD="align: center"]MN[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]CA[/TD]
[TD="align: center"]200-299[/TD]
[TD="align: center"]ME[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]NH[/TD]
[TD="align: center"][/TD]
[TD="align: center"]LO[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]CA[/TD]
[TD="align: center"]500-599[/TD]
[TD="align: center"]IJ[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]CA[/TD]
[TD="align: center"]600-699[/TD]
[TD="align: center"]UY[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]CA[/TD]
[TD="align: center"]800-899[/TD]
[TD="align: center"]PL[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]CA[/TD]
[TD="align: center"]900-999[/TD]
[TD="align: center"]UJ[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]AZ[/TD]
[TD="align: center"]1-63[/TD]
[TD="align: center"]MN[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]AZ[/TD]
[TD="align: center"]64-389[/TD]
[TD="align: center"]OP[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]AZ[/TD]
[TD="align: center"]390-550[/TD]
[TD="align: center"]LA[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]AZ[/TD]
[TD="align: center"]551-799[/TD]
[TD="align: center"]SF[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]NH[/TD]
[TD="align: center"]54-95[/TD]
[TD="align: center"]LO[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]NH[/TD]
[TD="align: center"]96-155[/TD]
[TD="align: center"]ER[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]NH[/TD]
[TD="align: center"]156-680[/TD]
[TD="align: center"]CO[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]NH[/TD]
[TD="align: center"]681-999[/TD]
[TD="align: center"]GH[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
Okay I edit your data set to make it easier to use,

G1: =IFERROR(INDEX($C$1:$C$15,MATCH(1,($A$1:$A$15=E1)*((LEFT($B$1:$B$15,FIND("-",$B$1:$B$15)-1))*1<=D1)*((RIGHT($B$1:$B$15,LEN($B$1:$B$15)-FIND("-",$B$1:$B$15)))*1>=D1),0)),"Not in range")

Confirm with Ctrl Shift Enter
Hope this help

 

[Sandeep Singh]

Thank you for your help.

when i copy and paste your formula, confirm with ctrl shift enter its working for me.

But when i tried keying the same formula in cell

=IFERROR(INDEX($C$2:$C$16,MATCH(1,($A$2:$A$16=E2)*((LEFT($B$2:$B$16,FIND("-",$B$2:$B$16)-1))((RIGHT($B$2:$B$16,LEN($B$2:$B$16)-FIND("-",$B$2:$B$16)))*1>=D2),0)),"Not in Range")

its not working for me, i confirm my formula with Ctrl shift enter it showing an error.

when i confirmed with Ctrl Shift Enter the formuala is started and ended with {....} flower brackets.

could you please tell me what can be the reason behind the error and about flower brackets {}.

Thank you for your help in advance.

 

[Special-K99]

{} indicates it is an Array Formula which is different from entering the formula as normal.
You MUST enter the formula using Ctrl-Shift-Enter.

 

[Sandeep Singh]

Thank you Special-K99 for your reply

I got to know about this now.

 

[yesterdays]

Thank you for your help.



=IFERROR(INDEX($C$2:$C$16,MATCH(1,($A$2:$A$16=E2)*((LEFT($B$2:$B$16,FIND("-",$B$2:$B$16)-1))((RIGHT($B$2:$B$16,LEN($B$2:$B$16)-FIND("-",$B$2:$B$16)))*1>=D2),0)),"Not in Range")

its not working for me, i confirm my formula with Ctrl shift enter it showing an error.


could you please tell me what can be the reason behind the error and about flower brackets {}.

Thank you for your help in advance.

You are missing part of the formula :
=IFERROR(INDEX($C$2:$C$16,MATCH(1,($A$2:$A$16=E2)*((LEFT($B$2:$B$16,FIND("-",$B$2:$B$16)-1))*1<=D2)*((RIGHT($B$2:$B$16,LEN($B$2:$B$16)-FIND("-",$B$2:$B$16)))*1>=D2),0)),"Not in Range")

 

[Sandeep Singh]

Thank you Yesterdays for your reply and correcting me.

The formula is working for me. A great help from you.

Thank You.

 

[Sandeep Singh]

Hi All,

I got the the formula to the above scenario.. my question is can be give conditional formatting on the formula result with data series.

Please find the data example below.

[TABLE="width: 211"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]ST[/TD]
[TD]Code[/TD]
[TD]Analyst
[/TD]
[/TR]
[TR]
[TD]NH
[/TD]
[TD]111[/TD]
[TD]ER
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 192"]
<colgroup><col style="width:48pt" span="3" width="64"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]CA[/TD]
[TD="class: xl67, width: 64"]1-99[/TD]
[TD="class: xl65, width: 64"]DH[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]CA[/TD]
[TD="class: xl65, width: 64"]100-199[/TD]
[TD="class: xl65, width: 64"]NH[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]CA[/TD]
[TD="class: xl65, width: 64"]200-299[/TD]
[TD="class: xl65, width: 64"]ME[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]CA[/TD]
[TD="class: xl65, width: 64"]500-599[/TD]
[TD="class: xl65, width: 64"]IJ[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]CA[/TD]
[TD="class: xl65, width: 64"]600-699[/TD]
[TD="class: xl65, width: 64"]UY[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]ca[/TD]
[TD="class: xl65, width: 64"]800-899[/TD]
[TD="class: xl65, width: 64"]PL[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]CA[/TD]
[TD="class: xl65, width: 64"]900-999[/TD]
[TD="class: xl65, width: 64"]UJ
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]AZ[/TD]
[TD="class: xl66, width: 64"]Jan-63[/TD]
[TD="class: xl65, width: 64"]MN[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]AZ[/TD]
[TD="class: xl65, width: 64"]64-389[/TD]
[TD="class: xl65, width: 64"]OP[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]AZ[/TD]
[TD="class: xl65, width: 64"]390-550[/TD]
[TD="class: xl65, width: 64"]LA[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]AZ[/TD]
[TD="class: xl65, width: 64"]551-799[/TD]
[TD="class: xl65, width: 64"]SF[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]NH[/TD]
[TD="class: xl65, width: 64"]54-95[/TD]
[TD="class: xl65, width: 64"]LO[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64"]NH
[/TD]
[TD="class: xl68, width: 64"]96-155
[/TD]
[TD="class: xl68, width: 64"]ER
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]NH[/TD]
[TD="class: xl65, width: 64"]156-680[/TD]
[TD="class: xl65, width: 64"]CO[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]NH[/TD]
[TD="class: xl65, width: 64"]681-999[/TD]
[TD="class: xl65, width: 64"]GH[/TD]
[/TR]
</tbody>[/TABLE]


The formula is in F5 Cell. Based on the result in F5 cell, code and the state cells should be formatted in a data series.

Hope the question is clear. Thank you for your help in Advance.

 

[MUHAMMAD IBRAHIM]

hello guys plz help me with following formula. because whenever i enter more 64 if commads it show an error with a message "the specified formula cannot be entered because it uses more than 64 levels of nesting". plz tell me how can i enter more than 64 if commads . my formula is

=IF(B3=1,Sheet1!$F$2,IF(B3=2,SHEET1!$F$3,IF(B3=3,SHEET1!$F$4,IF(B3=4,SHEET1!$F$5,IF(B3=5,SHEET1!$F$6,IF(B3=6,SHEET1!$F$7,IF(B3=7,Sheet1!$F$8,IF(B3=8,SHEET1!$F$9,IF(B3=9,SHEET1!$F$10,IF(B3=10,SHEET1!$F$11,IF(B3=11,SHEET1!$F$12,IF(B3=12,SHEET1!$F$13,IF(B3=13,Sheet1!$F$14,IF(B3=14,SHEET1!$F$15,IF(B3=15,SHEET1!$F$16,IF(B3=16,SHEET1!$F$17,IF(B3=17,SHEET1!$F$18,IF(B3=18,SHEET1!$F$19,IF(B3=19,Sheet1!$F$20,IF(B3=20,SHEET1!$F$21,IF(B3=21,SHEET1!$F$22,IF(B3=22,SHEET1!$F$23,IF(B3=23,SHEET1!$F$24,IF(B3=24,SHEET1!$F$25,IF(B3=25,Sheet1!$F$26,IF(B3=26,SHEET1!$F$27,IF(B3=27,SHEET1!$F$28,IF(B3=28,SHEET1!$F$29,IF(B3=29,SHEET1!$F$30,IF(B3=30,SHEET1!$F$31,IF(B3=31,Sheet1!$F$32,IF(B3=32,SHEET1!$F$33,IF(B3=33,SHEET1!$F$34,IF(B3=34,SHEET1!$F$35,IF(B3=35,SHEET1!$F$36,IF(B3=36,SHEET1!$F$37,IF(B3=37,Sheet1!$F$38,IF(B3=38,SHEET1!$F$39,IF(B3=39,SHEET1!$F$40,IF(B3=40,SHEET1!$F$41,IF(B3=41,SHEET1!$F$42,IF(B3=42,SHEET1!$F$43,IF(B3=43,Sheet1!$F$44,IF(B3=44,SHEET1!$F$45,IF(B3=45,SHEET1!$F$46,IF(B3=46,SHEET1!$F$47,IF(B3=47,SHEET1!$F$48,IF(B3=48,SHEET1!$F$49,IF(B3=49,Sheet1!$F$50,IF(B3=50,SHEET1!$F$3,IF(B3=51,SHEET1!$F$52,IF(B3=52,SHEET1!$F$53,IF(B3=53,SHEET1!$F$54,IF(B3=54,SHEET1!$F$55,IF(B3=55,Sheet1!$F$56,IF(B3=56,SHEET1!$F$57,IF(B3=57,SHEET1!$F$58,IF(B3=58,SHEET1!$F$59,IF(B3=59,SHEET1!$F$60,IF(B3=60,SHEET1!$F$61,IF(B3=61,Sheet1!$F$62,IF(B3=62,SHEET1!$F$63,IF(B3=63,SHEET1!$F$64,IF(B3=64,SHEET1!$F$65)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

 

[steve the fish]

You could use:

=INDIRECT("Sheet1!F"&B3+1)