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

• Sep 7, 2005

• #2

KatiLynSki

Civil/Environmental

May 16, 2005

33

US

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

 

Upvote 0 Downvote

• Sep 7, 2005

• #3

iken

Mechanical

May 13, 2003

151

NZ

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"

 

Upvote 0 Downvote

• Sep 7, 2005

• #4

SacreBleu

Structural

Apr 7, 2005

427

US

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.

 

Upvote 0 Downvote

• Sep 7, 2005

1
• #5

Kwan

Aerospace

Feb 11, 2005

252

US

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.

 

Upvote 0 Downvote

• Sep 8, 2005

• #6

MortenA

Chemical

Aug 20, 2001

2,998

DK

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

 

Upvote 0 Downvote

• Sep 8, 2005

• #7

Guidoo

Chemical

Sep 13, 2002

266

NL

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...

 

Upvote 0 Downvote

• Sep 8, 2005

• #8

francesca

Civil/Environmental

May 18, 2005

1,413

US

Do "ElseIf" statements count as If statements?

 

Upvote 0 Downvote

• Sep 8, 2005

• #9

Kwan

Aerospace

Feb 11, 2005

252

US

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)

 

Upvote 0 Downvote

• Sep 8, 2005

• #10

UcfSE

Structural

Dec 27, 2002

2,525

US

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.

 

Upvote 0 Downvote

• Sep 11, 2005

• #11

EngineNut

Automotive

Nov 29, 2002

2

US

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

 

Upvote 0 Downvote

• Sep 21, 2005

• Thread starter
• #12

MonicaLee

Mechanical

Jan 3, 2005

63

US

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!

 

Upvote 0 Downvote

• Sep 22, 2005

• #13

Clyde38

Electrical

Oct 31, 2003

533

US

How about the =CHOOSE()funtion?

 

Upvote 0 Downvote