Little tricky

[hsandeep]

I have 7 ‘main’ columns (headings: Col1, Col2, Col3, Col4, Col5, Col6, Col7) with values either of 100 or 200.
Each of the above ‘main’ columns has ‘adjacent’ columns to itself (headings: Adj1, Adj 2, Adj 3, Adj 4, Adj 5, Adj 6, Adj 7).

Output required: (in O2:O999) satisfying following conditions

O2= Check ‘main’ columns in row#2 containing value 100. Get the HIGHEST value of the corresponding ‘adjacent’ columns. If there are more than 1 highest value, then get the HIGHEST value of the corresponding ‘adjacent’ columns which appears at the right hand side.

Similarly for O3:O999

Thanks for the help rendered in advance.

Sheet1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

Col1

Adj1

Col2

Adj2

Col3

Adj3

Col4

Ad4

Col5

Adj5

Col6

Adj6

Col7

Adj7

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 34px"><col style="WIDTH: 49px"><col style="WIDTH: 34px"><col style="WIDTH: 49px"><col style="WIDTH: 34px"><col style="WIDTH: 49px"><col style="WIDTH: 34px"><col style="WIDTH: 56px"><col style="WIDTH: 34px"><col style="WIDTH: 56px"><col style="WIDTH: 34px"><col style="WIDTH: 49px"><col style="WIDTH: 34px"><col style="WIDTH: 49px"><col style="WIDTH: 62px"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #ffff00"]ANSWER[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]252.23[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]254.65[/TD]
[TD="align: right"]100[/TD]
[TD="bgcolor: #ffff00, align: right"]256.89[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]251.02[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]248.98[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]260.99[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]299.99[/TD]
[TD="bgcolor: #ffff00, align: right"]256.89[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]785.99[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]287.44[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]265.88[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]785.99[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]1000.36[/TD]
[TD="align: right"]100[/TD]
[TD="bgcolor: #ffff00, align: right"]785.99[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]201.02[/TD]
[TD="bgcolor: #ffff00, align: right"]785.99[/TD]

</tbody>

 

[hsandeep]

There are 7 switches: A1, C1, E1, G1, I1, K1 & M1. If any of these contains Y then ONLY the corresponding column has to be checked for 100 & adjacent column to be taken into consideration for MAX.

 

[sheetspread]

Like

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Y	Adj1	Y	Adj2	Y	Adj3		Ad4		Adj5	Y	Adj6		Adj7	ANSWER
2	200	252.23	100	254.65	100	256.89	200	251.02	200	248.98	200	260.99	200	299.99	256.89
3	100	785.99	200	287.44	100	265.88	100	900.88	200	1000.36	100	785.99	200	201.02	785.99
Sheet10

Cell Formulas
Range	Formula
O2	{=MAX(IF($A$1:$M$1="Y",IF(A2:M2=100, B2:N2)))}
O3	{=MAX(IF($A$1:$M$1="Y",IF(A3:M3=100, B3:N3)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

? (just added to shg's formula)

 

[hsandeep]

It WORKS. But please tell me for row#3, is your formula picking B3 or L3. I would always require cell value which is at the right hand side most. (here L3 is on the right hand side of B3) even though the values are same.

I don't know whether this is possible or not?

 

[sheetspread]

I don't think the formula considers the position at all. Are you saying you don't want the max value, but the max column (rightmost cell)?

 

[hsandeep]

I don't think the formula considers the position at all. Are you saying you don't want the max value, but the max column (rightmost cell)?

I have thought of a solution....

There would be 2 kind of switches in row#1...Y or Z (for ON i.e. the corresponding column MUST BE considered) blank (for OFF i.e. the corresponding column SHOULD NOT BE considered) & out of Y & Z, Z has to be considered.

i.e. if Z appears in row#1 then all such Z columns has to be considered for pulling the MAX values from the 'adjacent' columns.
If Z does not appears then look for Y & get the MAX values......
if both Z Y does not appears then null.

 

[sheetspread]

This?

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Y	Adj1	Y	Adj2	Y	Adj3	Y	Ad4	Y	Adj5	Y	Adj6		Adj7	ANSWER
2	200	252.23	100	254.65	100	256.89	100	251.02	200	248.98	200	260.99	200	299.99	256.89
3	100	785.99	200	287.44	100	265.88	100	900.88	200	1000.36	100	785.99	200	201.02	900.88
Sheet10

Cell Formulas
Range	Formula
O2	{=IF(COUNTIF($A$1:$N$1,"Z")>0,MAX(IF($A$1:$M$1="Z",IF(A2:M2=100,B2:N2))),IF(COUNTIF($A$1:$N$1,"Y")>0,MAX(IF($A$1:$M$1="Y",IF(A2:M2=100,B2:N2))),""))}
O3	{=IF(COUNTIF($A$1:$N$1,"Z")>0,MAX(IF($A$1:$M$1="Z",IF(A3:M3=100,B3:N3))),IF(COUNTIF($A$1:$N$1,"Y")>0,MAX(IF($A$1:$M$1="Y",IF(A3:M3=100,B3:N3))),""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[hsandeep]

Thanks spreadsheet, that WORKS.