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.

CA	1-99	DH
	100-199	NH
	200-299	ME
	500-599	IJ
	600-699	UY
	800-899	PL
	900-999	UJ
AZ	1-64	MN
	64-389	OP
	390-550	LA
	551-799	SF
NH	54-95	LO
	96-155	ER
	156-680	CO
	681-999	GH

<tbody>
</tbody>

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]

	A	B	C	D	E	F	G
1	CA	1-99	DH	2	CA		DH
2	CA	100-199	NH	1	AZ		MN
3	CA	200-299	ME	55	NH		LO
4	CA	500-599	IJ
5	CA	600-699	UY
6	CA	800-899	PL
7	CA	900-999	UJ
8	AZ	1-63	MN
9	AZ	64-389	OP
10	AZ	390-550	LA
11	AZ	551-799	SF
12	NH	54-95	LO
13	NH	96-155	ER
14	NH	156-680	CO
15	NH	681-999	GH

<tbody>
</tbody>

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.

ST	Code	Analyst
NH	111	ER

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

CA	1-99	DH
CA	100-199	NH
CA	200-299	ME
CA	500-599	IJ
CA	600-699	UY
ca	800-899	PL
CA	900-999	UJ
AZ	Jan-63	MN
AZ	64-389	OP
AZ	390-550	LA
AZ	551-799	SF
NH	54-95	LO
NH	96-155	ER
NH	156-680	CO
NH	681-999	GH

<colgroup><col style="width:48pt" span="3" width="64"> </colgroup><tbody>
</tbody>

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)