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
Monica Lee





RE: Can we have Nested Loop IF, have more than 7 times?
-KLD
RE: Can we have Nested Loop IF, have more than 7 times?
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?
RE: Can we have Nested Loop IF, have more than 7 times?
RE: Can we have Nested Loop IF, have more than 7 times?
Best regards
Morten
RE: Can we have Nested Loop IF, have more than 7 times?
RE: Can we have Nested Loop IF, have more than 7 times?
RE: Can we have Nested Loop IF, have more than 7 times?
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?
RE: Can we have Nested Loop IF, have more than 7 times?
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?
RE: Can we have Nested Loop IF, have more than 7 times?