Nested Functions

Flight012 · May 21st, 2002, 01:08 PM

I was wondering is there anyway to nest more then 7 functions together. I have just about completed a new form and have a 2 columns that I am working with. They both have 36 rows in them, and the functions are very basic, ie: if a1=b1,c1, and so on. I now am having a problem in the fact that I need to nest 36 - 40 of these to get the form completed.

Thanks,
Greg

WoodyHays · May 21st, 2002, 01:30 PM

I assume you are talking about IF statements.

The Excel limit is 7 generations in a statement. This has caused me a lot of grief, but it usually forces me to rethink my formulas which leads to a better method.

Flight012 · May 21st, 2002, 01:32 PM

Yes it is IF statements. I was looking into trying to use VLOOKUP, but I need to take a look and see if that will really work for me.

Thanks,
Greg

Aladin Akyurek · May 21st, 2002, 01:33 PM

Quote:

On 2002-05-21 12:08, Flight012 wrote:
I was wondering is there anyway to nest more then 7 functions together. I have just about completed a new form and have a 2 columns that I am working with. They both have 36 rows in them, and the functions are very basic, ie: if a1=b1,c1, and so on. I now am having a problem in the fact that I need to nest 36 - 40 of these to get the form completed.

Thanks,
Greg

Try to re-express any regularity the condition and action parts of those IFs must have in a diffrent form. Long chain of IFs even if they are nested can be rewritten as a lookup expression, most notably in terms of VLOOKUP.

Perhaps you would care to post the current IF-formula along with a short description what it's intended to do.

Flight012 · May 21st, 2002, 01:42 PM

Here is the formula if I was able to nest 36-40 if statements together. Right now I am stuck on the maximum of 7 if statements. H17 is a drop-down box containing a name, AX1-AX40 contains names, AY1-AY40 contains a corresponding number for the name.

=IF(H17=AX1,AY1,IF(H17=AX2,AY2,IF H17=AX3,AY3,IF(H17=AX4,AY4,IF(H17=AX5,AY5,IF(H17=AX6,AY6,IF(H17=AX7,AY7,(H17=AX8,AY8,IF(H17=AX9,AY9,IF(H17=AX10,AY10,IF( H17=AX11,AY11,IF(H17=AX12,AY12,IF(H17=AX13,AY13,IF (H17=AX14,AY14,IF(H17=AX15,AY15,IF(H17=AX16,AY16,IF(H17=AX17,AY17,IF(H17=AX18,AY18,IF(H17=AX19,AY19,IF(H17=AX20,AY20,IF( H17=AX21,AY21,IF(H17=AX22,AY22,IF(H17=AX23,AY23,IF(H17=AX24,AY24,IF(H17=AX25,AY25,IF(H17=AX26,AY26,IF(H17=AX27,AY27,IF(H 17=AX28,AY28,IF(H17=AX29,AY29,IF(H17=AX30,AY30,IF(H17=AX31,AY31,IF(H17=AX32,AY32,IF(H17=AX33,AY33,IF(H17=AX34,AY34,IF(H1 7=AX35,AY35,IF(H17=AX36,AY36,IF(H17=AX37,AY37,IF(H17=AX38,AY38,IF(H17=AX39,AY39,IF(H17=AX40,AY40)))))))))))))))))))))))) ))))))))))))))))

Thanks,
Greg

Aladin Akyurek · May 21st, 2002, 01:46 PM

Quote:

On 2002-05-21 12:42, Flight012 wrote:
Here is the formula if I was able to nest 36-40 if statements together. Right now I am stuck on the maximum of 7 if statements. H17 is a drop-down box containing a name, AX1-AX40 contains names, AY1-AY40 contains a corresponding number for the name.

=IF(H17=AX1,AY1,IF(H17=AX2,AY2,IF H17=AX3,AY3,IF(H17=AX4,AY4,IF(H17=AX5,AY5,IF(H17=AX6,AY6,IF(H17=AX7,AY7,(H17=AX8,AY8,IF(H17=AX9,AY9,IF(H17=AX10,AY10,IF( H17=AX11,AY11,IF(H17=AX12,AY12,IF(H17=AX13,AY13,IF (H17=AX14,AY14,IF(H17=AX15,AY15,IF(H17=AX16,AY16,IF(H17=AX17,AY17,IF(H17=AX18,AY18,IF(H17=AX19,AY19,IF(H17=AX20,AY20,IF( H17=AX21,AY21,IF(H17=AX22,AY22,IF(H17=AX23,AY23,IF(H17=AX24,AY24,IF(H17=AX25,AY25,IF(H17=AX26,AY26,IF(H17=AX27,AY27,IF(H 17=AX28,AY28,IF(H17=AX29,AY29,IF(H17=AX30,AY30,IF(H17=AX31,AY31,IF(H17=AX32,AY32,IF(H17=AX33,AY33,IF(H17=AX34,AY34,IF(H1 7=AX35,AY35,IF(H17=AX36,AY36,IF(H17=AX37,AY37,IF(H17=AX38,AY38,IF(H17=AX39,AY39,IF(H17=AX40,AY40)))))))))))))))))))))))) ))))))))))))))))

Thanks,
Greg

Try:

=IF(COUNTIF($AX$1:$AX$40,H17),VLOOKUP(H17,$AX$1:$AY$40,2,0),"Not Found")

instead.

Addendum. If any selection from the dropdown list in H17 is guaranteed to exist in AX1:AX40, use the shoter formula:

=VLOOKUP(H17,$AX$1:$AY$40,2,0)

[ This Message was edited by: Aladin Akyurek on 2002-05-21 12:49 ]

[ This Message was edited by: Aladin Akyurek on 2002-05-21 13:16 ]

Troy · May 21st, 2002, 01:48 PM

I think this would be perfect for vlookup
Try this

=VLOOKUP(H1,AX1:AY40,2,FALSE)

Flight012 · May 21st, 2002, 01:49 PM

Thank you!

That worked perfect. I guess I need to look more into VLOOKUP.

Thanks again.

Greg

Gary_E · May 21st, 2002, 02:03 PM

I'm sure others have much easier solutions, but if your keen on using the if stmts, this has always worked for me in a pinch. Basically, it just adds a 0 to the value you want.

Value = numeric
=IF(H17=AX1,AY1,0)+IF(H17=AX2,AY2,0)+IF H17=AX3,AY3,0)+......
Value = text
=IF(H17=AX1,AY1,"")&IF(H17=AX2,AY2,"")&IF H17=AX3,AY3,"")+......

Jay Petrulis · May 21st, 2002, 02:07 PM

Another way...

=INDIRECT("AY" & MATCH(H17,AX1:AX40,0))

[ This Message was edited by: Jay Petrulis on 2002-05-21 13:08 ]

May 21st, 2002, 02:03 PM	#9
Gary_E Board Regular Join Date: Feb 2002 Location: Charlotte, NC USA Posts: 108	I'm sure others have much easier solutions, but if your keen on using the if stmts, this has always worked for me in a pinch. Basically, it just adds a 0 to the value you want. Value = numeric =IF(H17=AX1,AY1,0)+IF(H17=AX2,AY2,0)+IF H17=AX3,AY3,0)+...... Value = text =IF(H17=AX1,AY1,"")&IF(H17=AX2,AY2,"")&IF H17=AX3,AY3,"")+...... __________________ Thxs for tips - this place is a great source for tips & tricks Gary

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 21st, 2002, 01:08 PM	#1
Flight012 New Member Join Date: May 2002 Posts: 4	I was wondering is there anyway to nest more then 7 functions together. I have just about completed a new form and have a 2 columns that I am working with. They both have 36 rows in them, and the functions are very basic, ie: if a1=b1,c1, and so on. I now am having a problem in the fact that I need to nest 36 - 40 of these to get the form completed. Thanks, Greg

May 21st, 2002, 01:30 PM	#2
WoodyHays Board Regular Join Date: May 2002 Location: Atlanta, GA Posts: 185	I assume you are talking about IF statements. The Excel limit is 7 generations in a statement. This has caused me a lot of grief, but it usually forces me to rethink my formulas which leads to a better method.

May 21st, 2002, 01:32 PM	#3
Flight012 New Member Join Date: May 2002 Posts: 4	Yes it is IF statements. I was looking into trying to use VLOOKUP, but I need to take a look and see if that will really work for me. Thanks, Greg

May 21st, 2002, 01:42 PM	#5
Flight012 New Member Join Date: May 2002 Posts: 4	Here is the formula if I was able to nest 36-40 if statements together. Right now I am stuck on the maximum of 7 if statements. H17 is a drop-down box containing a name, AX1-AX40 contains names, AY1-AY40 contains a corresponding number for the name. =IF(H17=AX1,AY1,IF(H17=AX2,AY2,IF H17=AX3,AY3,IF(H17=AX4,AY4,IF(H17=AX5,AY5,IF(H17=AX6,AY6,IF(H17=AX7,AY7,(H17=AX8,AY8,IF(H17=AX9,AY9,IF(H17=AX10,AY10,IF( H17=AX11,AY11,IF(H17=AX12,AY12,IF(H17=AX13,AY13,IF (H17=AX14,AY14,IF(H17=AX15,AY15,IF(H17=AX16,AY16,IF(H17=AX17,AY17,IF(H17=AX18,AY18,IF(H17=AX19,AY19,IF(H17=AX20,AY20,IF( H17=AX21,AY21,IF(H17=AX22,AY22,IF(H17=AX23,AY23,IF(H17=AX24,AY24,IF(H17=AX25,AY25,IF(H17=AX26,AY26,IF(H17=AX27,AY27,IF(H 17=AX28,AY28,IF(H17=AX29,AY29,IF(H17=AX30,AY30,IF(H17=AX31,AY31,IF(H17=AX32,AY32,IF(H17=AX33,AY33,IF(H17=AX34,AY34,IF(H1 7=AX35,AY35,IF(H17=AX36,AY36,IF(H17=AX37,AY37,IF(H17=AX38,AY38,IF(H17=AX39,AY39,IF(H17=AX40,AY40)))))))))))))))))))))))) )))))))))))))))) Thanks, Greg

May 21st, 2002, 01:48 PM	#7
Troy New Member Join Date: Apr 2002 Posts: 47	I think this would be perfect for vlookup Try this =VLOOKUP(H1,AX1:AY40,2,FALSE)

May 21st, 2002, 01:49 PM	#8
Flight012 New Member Join Date: May 2002 Posts: 4	Thank you! That worked perfect. I guess I need to look more into VLOOKUP. Thanks again. Greg

May 21st, 2002, 02:07 PM	#10
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	Another way... =INDIRECT("AY" & MATCH(H17,AX1:AX40,0)) [ This Message was edited by: Jay Petrulis on 2002-05-21 13:08 ]