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.

 

[MUHAMMAD IBRAHIM]

Click on the "Post a new thread" button at the top of this page:

Excel Questions

but i have already inbox you my problem.

 

[FormR]

but i have already inbox you my problem.

Please keep your questions on the public forum and post a new thread for new questions.

 

[MUHAMMAD IBRAHIM]

Please keep your questions on the public forum and post a new thread for new questions.

ok but i have also post a new thread which i want to ask click the link .. plz answer me. http://www.mrexcel.com/forum/excel-...-me-following-problem-let-me-explain-you.html

 

[MUHAMMAD IBRAHIM]

i dont want D16 to change to D17

i am using the following formula "=L5-'cutting stocks inventory'!D16" in my spreadsheet . but when i drag the formula it changes L5 to L6 and D16 to D17 and so on . what i want to do is i dont want D16 to change to D17 i want only D16 to remain same for every row . not L5 plz help me out

 

[FormR]

Re: i dont want D16 to change to D17

New questions deserve new threads!

=L5-'cutting stocks inventory'!D$16