×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Can we have Nested Loop IF, have more than 7 times?

## Can we have Nested Loop IF, have more than 7 times?

(OP)
Nesting level limits indicated a formula can contain up to seven levels of nested functions. Is there anyway I can loop more than 10 times because I am calculating a loop for 10 different sizes of pipeline for the gas loss calculation. Can someone please help because this is very critical to be able to include all pipe diameters and there is 10 sizes of pipe. Thank you

Monica Lee

### RE: Can we have Nested Loop IF, have more than 7 times?

Have you tried looking at excel's VLOOKUP command? You just have to make a table with the values you want associated with each pipe diameter. There are a ton of examples of using VLOOKUP out on the internet or in the help files.

-KLD

### RE: Can we have Nested Loop IF, have more than 7 times?

Why don't you use the "vlookup" function.

You can have all ten claculations, and pick the appropriate one depending on the pipe size using "vlookup"

### RE: Can we have Nested Loop IF, have more than 7 times?

I agree with the above posts recommneding the VLOOKUP commands. However, if you absolutely wish to nest more than 7 IF statements, then nest 6, with the remaining statements "chained" to another cell.

### RE: Can we have Nested Loop IF, have more than 7 times?

Another way to nest these loops is to use visual basic.  You can create your own equation in VB with your nested if-then statements then call up the equation in your cell on the worksheet.  I use this even if I nest just a couple times because it removes my confusion.

### RE: Can we have Nested Loop IF, have more than 7 times?

also the "case" statement (assuming that we allready are in VB) might be another way to make a less complicated selection structure?

Best regards

Morten

### RE: Can we have Nested Loop IF, have more than 7 times?

I fully agree with Kwan about using VB. When you nest more than 2 or 3 IF loops in Excel it becomes almost impossible to understand. Using the trick is proposed by SacreBleu (chaining two cells with nested IF loops) would make it even worse...

### RE: Can we have Nested Loop IF, have more than 7 times?

Do "ElseIf" statements count as If statements?

### RE: Can we have Nested Loop IF, have more than 7 times?

In VB you can use if else statements.  Looks like this:

Function my_equ(a)

If a < 1 then
my_equ = "Less than 1 inch"
else
my_equ = "1 inch or more"
End if

End Function

Then in your cell write =my_equ(A5)

### RE: Can we have Nested Loop IF, have more than 7 times?

I agree with using the "lookup" function versus nested if statements, or VB.  Even if you understand all those if's now, in a year or 6 months when you return to the sheet it's horrible trying to follow those again and understand what you did.

### RE: Can we have Nested Loop IF, have more than 7 times?

Found the below at... http://jwalk.com/ss/excel/usertips/tip080.htm

Using the CONCATENATE function
Yet another option was suggested to me by B. Ganesh: Use the CONCATENATE function. In this case, each argument for CONCATENATE consists of an IF function.
Here's an example:
=CONCATENATE(IF(A1="A",1,""),IF(A1="B",2,""),IF(A1="C",3,""),
IF(A1="D",4,""),IF(A1="E",5,""),IF(A1="F",6,""),IF(A1="G",7,""),
IF(A1="H",8,""),IF(A1="I",9,""),IF(A1="J",10,""))
The CONCATENATE function can handle as many as 30 arguments -- which equates to testing 30 different conditions.
And, as Alan Williams pointed out, you can avoid using the CONCATENATE function and use the concatenation operator (&):
=IF(A1="A",1,"")&IF(A1="B",2,"")&IF(A1="C",3,"")
&IF(A1="D",4,"")&IF(A1="E",5,"")&IF(A1="F",6,"")
&IF(A1="G",7,"")&IF(A1="H",8,"")&IF(A1="I",9,"")
&IF(A1="J",10,"")
This method is not limited to 30 comparisons.

Ron

### RE: Can we have Nested Loop IF, have more than 7 times?

(OP)
Thank you very much people, you are all very helpful. I was devastated by the Katrina hurricane so I couldn't reply anyone on time to thank for your time. I will now face again another disaster, Rita. God Bless!

### RE: Can we have Nested Loop IF, have more than 7 times?

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!