Optimize a formula

[jpfuse]

I can't seem to wrap my head around a more robust method to the formula i have created. If anyone has time and can think of something, it would be much appreciated.

Given the following data:

<TABLE style="WIDTH: 120pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=158><COLGROUP><COL style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 512" width=14><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><COL style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 512" width=14><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 11pt; HEIGHT: 13.5pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl74 height=18 width=14></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 49pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl78 width=65>A</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 11pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl76 width=14></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 49pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl79 width=65>B</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl74 height=17>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl77>IS</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl72></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl75>HS BKR 1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl74 height=17>2</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl77>IT</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl72></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl75>HS BKR 2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl74 height=17>3</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl77>IU</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl72></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl75>LS BKR 1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl74 height=17>4</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl77>IW</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl72></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl75>LS BKR 2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl74 height=17>5</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl77>IX</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl72></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl75>TERT</TD></TR></TBODY></TABLE>

I need to create a formula to determine an element depending on what is chosen for each row in column B. For example, to chose an "HS" element, since B1 and B2 both start with "HS", my element is IMAXSTF, because in column A1 and A2 is IS and IT. Sometimes there will be no combination, for example:

<TABLE style="WIDTH: 120pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=158><COLGROUP><COL style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 512" width=14><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><COL style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 512" width=14><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 11pt; HEIGHT: 13.5pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl74 height=18 width=14></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 49pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl78 width=65>A</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 11pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl76 width=14></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 49pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl79 width=65>B</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl74 height=17>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl77>IS</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl72></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl75>HS BKR 1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl74 height=17>2</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl77>IT</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl72></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl75>LS BKR 1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl74 height=17>3</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl77>IU</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl72></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl75>NC</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl74 height=17>4</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl77>IW</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl72></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl75>NC</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl74 height=17>5</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl77>IX</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl72></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl75>TERT</TD></TR></TBODY></TABLE>

For this data, the element for "HS" would simply be "IMAXSF".
My ridiculous formula that I came up with for the first set of data is:

=IF(SUMPRODUCT((COUNTIF(B1:B5,"HS"&"*")>1)+0)>0,IF(AND(LEFT(B1,2)="HS",LEFT(B2,2)="HS"),"IMAXSTF",IF(AND(LEFT(B2,2)="HS",LEFT(B3,2)="HS"),"IMAXTUF",IF(AND(LEFT(B3,2)="HS",LEFT(B4,2)="HS"),"IMAXUWF",IF(AND(LEFT(B4,2)="HS",LEFT(B5,2)="HS"),"IMAXWXF")))),IF(LEFT(B1,2)="HS","IMAXSF",IF(LEFT(B2,2)="HS","IMAXTF",IF(LEFT(B3,2)="HS","IMAXUF",IF(LEFT(B4,2)="HS","IMAXWF",IF(LEFT(B5,2)="HS","IMAXXF","NO HS DEFINED"))))))

The actual formula is quite a bit longer due to links to another sheet. There must be a better way.

 

[Ralajer]

The following is not as pretty as I would like but it is fairly simple and somewhat more robust in that it is not limited to two HS matches and can more easily expanded, if those are in fact concerns. It should be easy to follow but let me know if you have questions.

=IF(SUMPRODUCT(--(LEFT(B1:B5,2)="HS"))=0,"NO HS DEFINED","IMAX"&IF(LEFT(B1,2)="HS","S","")&IF(LEFT(B2,2)="HS","T","")&IF(LEFT(B3,2)="HS","U","")&IF(LEFT(B4,2)="HS","W","")&IF(LEFT(B5,2)="HS","X","")&"F")

 

[jpfuse]

the first part confused me a bit but i did some studying. tell me if i got it right.

the first part, LEFT(B1:B5,2)="HS", creates an array of TRUE/FALSEs by checking to see if the first two letters are HS. the "--" in front confused me at first but then i realize that is simply turning the TRUE/FALSEs into 1/0s. big duh moment for me...LOL. i think one reason why i was confused is because the SUMPRODUCT function only had one array argument. i figured that if you only enter one array, it uses an identity array as the second argument right?....for example the formula came down to "SUMPRODUCT({1,1,0,0,0})" which would be the as "SUMPRODUCT({1,1,0,0,0},(1,1,1,1,1})" .... right? i am a big newbie when it comes to arrays and array formulas, but im learning. they are very handy tools.

your formula is much more compact and robust than mine but i didnt mention several limitations. You CANNOT have more than two combinations and also the combinations must be consecutive, ie, the only options are "ST", "TU", "UW", or "WX". My formula did account for these limitations, though not very efficiently.

i thank you for looking at this for me. any other thoughts, please let me know.

 

[Ralajer]

I sort of inferred the limitations by examining the formula but I was not sure. The following is more complex but easier to add to then the nested ifs once you understand it. It's an array formula and has to be entered with CTRL+SHIFT+ENTER. Honestly I don't know if it is any better than the nested if's, especially if you don't plan on adding rows.

=CHOOSE(SUMPRODUCT(--(LEFT(B1:B5,2)="HS"))+1,"No HS Defined",CHOOSE(MAX(--(LEFT(B1:B5,2)="HS")*ROW($A$1:$A$5)),"IMAXSF","IMAXTF","IMAXUF","IMAXWF","IMAXXF"),IF(LARGE(--(LEFT(B1:B5,2)="HS")*ROW($A$1:$A$5),1)-LARGE(--(LEFT(B1:B5,2)="HS")*ROW($A$1:$A$5),2)=1,CHOOSE(LARGE(--(LEFT(B1:B5,2)="HS")*ROW($A$1:$A$5),2),"IMAXSTF","IMAXTUF","IMAXUWF","IMAXWXF"),"2 Non-Consecutive HS"),"3 HS","4 HS","5 HS")

Breakdown

[COLOR="Red"]'// CHOOSE Returns the value corresponding to the Index in the first argument[/COLOR]
CHOOSE(
	[COLOR="Red"]'// CHOOSE INDEX the count of HS in range B1:B5 + 1[/COLOR]
	SUMPRODUCT(--(LEFT(B1:B5,2)="HS"))+1, 
		[COLOR="Red"]'// When Index = 1 (0 HS's)[/COLOR]
		"No HS Defined", 
		[COLOR="Red"]'// When Index = 2 (1 HS)[/COLOR]
		CHOOSE(
			[COLOR="Red"]'// CHOOSE INDEX position in the array of the HS match[/COLOR]
			MAX(--(LEFT(B1:B5,2)="HS")*ROW($A$1:$A$5)),
				[COLOR="Red"]'// When Index = 1 to 5[/COLOR]
				"IMAXSF","IMAXTF","IMAXUF","IMAXWF","IMAXXF"),
		[COLOR="Red"]'// When Index = 3 (2 HS)[/COLOR]
		[COLOR="Red"]'// LARGE(Array,1) Returns Largest & LARGE(Array,2) Returns 2nd Largest[/COLOR]
		[COLOR="Red"]'// The if the two large are 1 apart they are consecutive[/COLOR]
		IF(LARGE(--(LEFT(B1:B5,2)="HS")*ROW($A$1:$A$5),1)-LARGE(--(LEFT(B1:B5,2)="HS")*ROW($A$1:$A$5),2)=1,
			[COLOR="Red"]'// From the 2nd largest determine what which to choose[/COLOR]
			CHOOSE(LARGE(--(LEFT(B1:B5,2)="HS")*ROW($A$1:$A$5),2),"IMAXSTF","IMAXTUF","IMAXUWF","IMAXWXF"),
			"2 Non-Consecutive HS"), 
                [COLOR="Red"]'// When Index = 4,5,6 (3,4,5 HS)[/COLOR]
		"3 HS","4 HS","5 HS")

NOTES
[COLOR="Red"]'// This is way to determine which row has the HS[/COLOR]
--(LEFT(B1:B5,2)="HS")*ROW($A$1:$A$5) 
[COLOR="Red"]'// {1,2,3,4,5} if all are HS or {0,0,3,0,5} if just B3 and B5 are HS etc [/COLOR]

 

[jpfuse]

wow....what a formula...will take me a little while to go through it and understand all the pieces. Thanks again for your help, it looks like it works like a charm.