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
1	Col1	Adj1	Col2	Adj2	Col3	Adj3	Col4	Ad4	Col5	Adj5	Col6	Adj6	Col7	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	785.99	200	1000.36	100	785.99	200	201.02	785.99

<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>
</tbody>

 

[sheetspread]

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.

Why does it matter? I think I understand what you meant to say but please explain.

 

[shg]

Maybe ...

Row\Col

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

1​

Col1​

Adj1​

Col2​

Adj2​

Col3​

Adj3​

Col4​

Ad4​

Col5​

Adj5​

Col6​

Adj6​

Col7​

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​

O2: {=MAX(IF(A2:M2=100, B2:N2))}

3​

100​

785.99​

200​

287.44​

100​

265.88​

100​

785.99​

200​

1000.36​

100​

785.99​

200​

201.02​

785.99​

 

[sheetspread]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Col1	Adj1	Col2	Adj2	Col3	Adj3	Col4	Ad4	Col5	Adj5	Col6	Adj6	Col7	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	785.99	200	1000.36	100	785.99	200	201.02	785.99
Sheet10

Cell Formulas
Range	Formula
O2	{=MAX(--(OFFSET(B2:N2,0,-1)=100)*B2:N2)}
O3	{=MAX(--(OFFSET(B3:N3,0,-1)=100)*B3:N3)}
Press CTRL+SHIFT+ENTER to enter array formulas.

Works on your sample data, but I'm not sure about the other condition you stated.

 

[shg]

{=MAX(--(OFFSET(B2:N2,0,-1)=100)*B2:N2)}

That would return 0 if the max value were negative.

 

[sheetspread]

Good catch, guess you do need the if (not sure why...)

 

[excelmane]

Sandeep bhai try this across O2:O999=MAX(IF(--(A2:M2=100)=1,OFFSET(A2:M2,0,1))) .... (Ctrl+Shift+Enter)
Worked for me for -ve MAX values also
Not considering column N for offset as it is 'Adjacent' and not 'Main'...

 

[hsandeep]

O2:O999=MAX(IF(--(A2:M2=100)=1,OFFSET(A2:M2,0,1))) .... (Ctrl+Shift+Enter)

I'll make my requirement 'real'. Please bear with me....

The 7 'main' columns are not in A to M but they are 'scattered' like column A is actually column LD; column C is MA; column E is column MH; column G is column NE; column I is column NP; column K is column NZ & column M is column OY.
The 'adjacent' columns are next to these 'main' columns.
To identify these 7 'main' columns, row#1 contains a value 10 which is like for column LD LD1=10; & so on till column OY OY1=10.

Any more clarification is welcomed.

 

[hsandeep]

I want to add excel's 'on/off switch' (in the row#1) to get the MAX value. For ON, Y is punched in row#1 else blank.

Sheet1

	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

<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>
</tbody>

How to get the HIGHEST value of the corresponding ‘adjacent’ columns considering the 'on/off switch'?

 

[sheetspread]

Just 1 switch? That highlights the max values adjacent to 100?