Excel Limits Embedded "If" Statements? Workaround?

[cameronWFA]

Hey Everyone

I am new to the forum, as I just began a project that is going to be very long and Excel "heavy." I am sure to have plenty of questions regarding Excel, and will do my best to answer as many as I can as I go as well.

My first question for the board is:

Excel seems to only allow 7 embedded arguments for a function. Is there a way around this? Basically, I have about 15 collumns of numbers, and at the top of each collumn is a header/name. The two last collumns, however, are important. The second to last is a function that prints the largest number across the collumns per row. E.g. A2,B2,C2, <-- whichever number is highest. The last collumn function (which is the one I am having trouble with), aims to test which collumn that "Highest Number" is from, and print out the Name/Header that is at the top. However, when I go past 7 embedded "if" statements, Excel freaks out. Here is the code:

=IF(DG26=CU26, $CU$25, (IF(DG26=CV26, $CV$25, (IF(DG26=CW26, $CW$25, (IF(DG26=CX26, $CX$25, (IF(DG26=CY26, $CY$25, (IF(DG26=CZ26, $CZ$25, (IF(DG26=DA26, $DA$25, "NULL")))))))))))))

Is there any way around this? Thanks in advance!

 

[pplstuff]

Yes, I believe 7 ifs are the limit. I work around by doing if(if(if(...))))))),0)+if(if(if(...)))))))

So if the result of one is 0, the other should be a number...

 

[Oeldere]

maybe you can put the results in a table and use VLookup.

 

[cameronWFA]

Thanks for the quick response! Sorry but I am a bit confused about what you mean. Are you saying to put 0 as the "else" argument for the first 7 "ifs", and then after reaching 7 add a + amd then continue on?

Thanks!

Cam

 

[T. Valko]

Hey Everyone

I am new to the forum, as I just began a project that is going to be very long and Excel "heavy." I am sure to have plenty of questions regarding Excel, and will do my best to answer as many as I can as I go as well.

My first question for the board is:

Excel seems to only allow 7 embedded arguments for a function. Is there a way around this? Basically, I have about 15 collumns of numbers, and at the top of each collumn is a header/name. The two last collumns, however, are important. The second to last is a function that prints the largest number across the collumns per row. E.g. A2,B2,C2, <-- whichever number is highest. The last collumn function (which is the one I am having trouble with), aims to test which collumn that "Highest Number" is from, and print out the Name/Header that is at the top. However, when I go past 7 embedded "if" statements, Excel freaks out. Here is the code:




Is there any way around this? Thanks in advance!

Try something like this...

Book1
	A	B	C	D	E	F
1	Header1	Header2	Header3	Header4	_	_
2	15	89	20	75	_	Header2
Sheet1

This formula entered in F2:

=INDEX(A1:D1,MATCH(MAX(A2:D2),A2:D2,0))

 

[pplstuff]

Are you saying to put 0 as the "else" argument for the first 7 "ifs", and then after reaching 7 add a + amd then continue on?

Exactly, this will work if you're dealing with numbers only.

If you're expecting a text result, set the else/false statement to "" and then use & instead of +. So, for text it would be:
=if(if(if()))),"")&if(if(if()))))"")

 

[cameronWFA]

Try something like this...

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Verdana,Arial; FONT-SIZE: 10pt" border=0 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 59px"><COL style="WIDTH: 59px"><COL style="WIDTH: 59px"><COL style="WIDTH: 59px"><COL style="WIDTH: 59px"><COL style="WIDTH: 59px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid">Header1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">Header2</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">Header3</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">Header4</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">15</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">89</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">20</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">75</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">Header2</TD></TR></TBODY></TABLE>


This formula entered in F2:

=INDEX(A1:D1,MATCH(MAX(A2:D2),A2:D2,0))

HUGE Thanks guys! I understand for the most part, conceptually, how that formula works. However, how come you must write A2:D2 again after the "MAX(A2:D2)? Also, is there a way to do this same thing for the SECOND highest value? I know that the Large function can be used for something like this but all the articles I am reading about it are just for selecting the Nth largest out of a data set in a collumn, not necessarily doing what I need it to do.

You guys are great! Thanks again!

Cam

 

[cameronWFA]

Nevermind! Got it figured out! Here is the code in case anyone runs into this type of problem in the future.

2nd Largest:
=INDEX($CU$25:$DF$25,MATCH(LARGE(CU26:DF26, 2),CU26:DF26,0))

Until next time!!!
Cam

 

[Oeldere]

This will do.

Excel Workbook
	E	F	G	H	I	J	K
1		Header1	Header2	Header3	Header4	Header5	_
2		2	15	89	20	75	_
3
4	large	Header3
5	second	Header5
Blad1

Cell Formulas
Range	Formula
F4	=INDEX(F1:J1,MATCH(MAX(F2:J2),F2:J2,0))
F5	=INDEX(F1:J1,MATCH(LARGE(F2:J2,2),F2:J2,0))

 

[T. Valko]

HUGE Thanks guys! I understand for the most part, conceptually, how that formula works. However, how come you must write A2:D2 again after the "MAX(A2:D2)? Also, is there a way to do this same thing for the SECOND highest value? I know that the Large function can be used for something like this but all the articles I am reading about it are just for selecting the Nth largest out of a data set in a collumn, not necessarily doing what I need it to do.

You guys are great! Thanks again!

Cam

You first must extract the max value. By doing that you're telling the MATCH function what to look for.

MATCH(MAX(A2:D2)

MATCH(89,

Then you have to tell it where to look for that max value:

MATCH(89,A2:D2

To return the column header that corresponds to the 2nd highest number:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 60px"><COL style="WIDTH: 60px"><COL style="WIDTH: 60px"><COL style="WIDTH: 60px"><COL style="WIDTH: 60px"><COL style="WIDTH: 60px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Header1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Header2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Header3</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Header4</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">15</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">89</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">20</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">75</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Header4</TD></TR></TBODY></TABLE>


This formula entered in F2:

=INDEX(A1:D1,MATCH(LARGE(A2:D2,2),A2:D2,0))