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.

 

[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