MrExcel.com - Your One Stop for Excel Tips & Solutions

How do I overcome the maximum of "7 if loops" in Excel 97

Posted by Dominic on May 24, 2001 2:57 AM

I have a price calculation model , where the price is based on many variables. What I also need to do is to create an option of 9 if loops
ie if milk price is between

<1 >3
>3 <5<p>etc and then a result and then that result is checked against another range of ifs

if result is
<7 >8 etc

Does anyone have suggestions for a Macro to do this, if not how do I beat the restriction of "7 ifs" in Excel 97

Posted by Dave Hawley on May 24, 2001 3:43 AM

Dominic

You would be better of Nesting a Select Case Statement. See below:


Sub NoLoops()
Select Case Range("A1")
Case 1 To 5
Select Case Range("A2")
Case 10 To 20
Select Case Range("A3")
Case 21 To 30
Select Case Range("A4")
Case 31 To 40
MsgBox "It is"
End Select
End Select
End Select
End Select

End Sub

Dave


OzGrid Business Applications

Posted by Aladin Akyurek on May 24, 2001 4:34 AM

Dominic

If interested in a formula-based solution, have a look at

Posted by Aladin Akyurek on March 28, 2001 at 15:38:26:

>In Reply To: IF function nesting limitations posted by Valerie on March 28, 2001 at 14:34:29:

>I converted 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))))))))))

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



You can also post a snippet of your data and the relevant conditions that you want to use.

Aladin

Posted by lenze on May 24, 2001 6:29 AM

See Chip Pearsons" Nested functions page for another approach

Posted by Aladin Akyurek on May 24, 2001 7:05 AM

Lenze

Expanding the number of nested IFs, as has been described on Pearson's page, is, in my humble opnion, the most harmful proposal in the world of spreadsheets. It's darn hard for human cognition to process 3 nested IFs. What is the point of setting up of say 14 nested IFs, if you can't hardly debug it when needed. That's the reason why I never brought up that "solution" on this site.

Cheers.

Aladin