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

[MonicaLee]

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

 

[KatiLynSki]

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

 

[iken]

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"

 

[SacreBleu]

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.

 

[Kwan]

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.

 

[MortenA]

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

 

[Guidoo]

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

 

[francesca]

Do "ElseIf" statements count as If statements?

 

[Kwan]

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)

 

[UcfSE]

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.

 

[EngineNut]

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

 

[MonicaLee]

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!

 

[Clyde38]

How about the =CHOOSE()funtion?