MrExcel Publishing
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


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


OzGrid Business Applications

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


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


& the following in a column next to the previous


& what follows in a column next to the previous


Select these 3-column of values and name it, e.g., VALUES.

Then use the following VLOOKUP-formula instead:


BTW, what are these A0, A1,etc.? Cell addresses? If so, replace them as required by Excel.


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


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

See Chip Pearsons" Page for another approach

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


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.