Sandeep Singh
New Member
- Joined
- Mar 13, 2013
- Messages
- 40
Hi All,
Hope Everyone is Doing Great !!!
Data on which I am applying formula.
<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.
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.