IF function nesting limitations
Posted by Valerie on March 28, 2001 2:34 PM
I cnverted a spreadshhet from Lotus, which had 12 IF arguments and it seems Excel has a limit of 7. Any suggestions? Here is my formula
=IF(AND(O4>24999,BW4=1),"A1",IF(AND(O4>24999,BW4=0),"A0",IF(AND(AND(O4<25000,O4>19999),BW4=1),"B1",IF(AND(AND(O4<25000,O4>19999),BW4=0),"B0",IF(AND(AND(O4<20000,O4>14999),BW4=1),"C1",IF(AND(AND(O4<20000,O4>14999),BW4=0),"C0",IF(AND(AND(O4<15000,O4>9999),BW4=1),"D1",IF(AND(AND(O4<15000,O4>9999),BW4=0),"D0",IF(AND(O4<10000,BW4=1),"E1",IF(AND(O4<10000,BW4=0),"E0",0))))))))))
Posted by Mark W. on March 28, 2001 3:22 PM
Question...
What's the domain of O4?
Posted by Aladin Akyurek on March 28, 2001 3:38 PM
Hi Valerie
I believe you can replace this giant-IF with something much simpler. If I understand it correctly (a hard job with this much ifs), you can construct a lookup table. I propose to place the following in one column
0
10000
15000
20000
25000
& the following in a column next to the previous
E0
D0
C0
B0
A0
& what follows in a column next to the previous
E1
D1
C1
B1
A1
Select these 3-column of values and name it, e.g., VALUES.
Then use the following VLOOKUP-formula instead:
=VLOOKUP(O4,VALUES,IF(BW4,3,2),1)
BTW, what are these A0, A1,etc.? Cell addresses? If so, replace them as required by Excel.
Aladin
Posted by Mark W. on March 28, 2001 3:42 PM
Aladin and I are heading down the same path...
Here's my 1st stab at it...
=VLOOKUP(O4,{0,"E";10000,"D";15000,"C";20000,"B";25000,"A"},2)&BW4
Posted by Aladin Akyurek on March 28, 2001 3:46 PM
Re: Aladin and I are heading down the same path...
No objection. The objective is to kill that monstruous IF if we can.
Posted by VALERIE on March 28, 2001 4:18 PM
Aladin, You ROCK!!! Thanks!!!
Posted by Mo on March 28, 2001 4:19 PM
Re: Question...
Hi there, try breaking your formula into two.
in A1 put
=IF(AND(O4>24999,BW4=1),"A1",IF(AND(O4>24999,BW4=0),"A0",IF(AND(AND(O4<25000,O4>19999),BW4=1),"B1",IF(AND(AND(O4<25000,O4>19999),BW4=0),"B0",IF(AND(AND(O4<20000,O4>14999),BW4=1),"C1",B1)))))
In B1 put
=IF(AND(AND(O4<20000,O4>14999),BW4=0),"C0",IF(AND(AND(O4<15000,O4>9999),BW4=1),"D1",IF(AND(AND(O4<15000,O4>9999),BW4=0),"D0",IF(AND(O4<10000,BW4=1),"E1",IF(AND(O4<10000,BW4=0),"E0",0)))))
This formula should work fine. Hope so
Mo
