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>

 

[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 ...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][td]

M​

[/td][td]

N​

[/td][td]

O​

[/td][td]

P​

[/td][/tr][tr][td]

1​

[/td][td]

Col1​

[/td][td]

Adj1​

[/td][td]

Col2​

[/td][td]

Adj2​

[/td][td]

Col3​

[/td][td]

Adj3​

[/td][td]

Col4​

[/td][td]

Ad4​

[/td][td]

Col5​

[/td][td]

Adj5​

[/td][td]

Col6​

[/td][td]

Adj6​

[/td][td]

Col7​

[/td][td]

Adj7​

[/td][td]

ANSWER​

[/td][td]

​

[/td][/tr]
[tr][td]

2​

[/td][td]

200​

[/td][td]

252.23​

[/td][td]

100​

[/td][td]

254.65​

[/td][td]

100​

[/td][td]

256.89​

[/td][td]

200​

[/td][td]

251.02​

[/td][td]

200​

[/td][td]

248.98​

[/td][td]

200​

[/td][td]

260.99​

[/td][td]

200​

[/td][td]

299.99​

[/td][td]

256.89​

[/td][td]O2: {=MAX(IF(A2:M2=100, B2:N2))}[/td][/tr]
[tr][td]

3​

[/td][td]

100​

[/td][td]

785.99​

[/td][td]

200​

[/td][td]

287.44​

[/td][td]

100​

[/td][td]

265.88​

[/td][td]

100​

[/td][td]

785.99​

[/td][td]

200​

[/td][td]

1000.36​

[/td][td]

100​

[/td][td]

785.99​

[/td][td]

200​

[/td][td]

201.02​

[/td][td]

785.99​

[/td][td][/td][/tr]
[/table]

 

[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

Adj1

Adj2

Adj3

Ad4

Adj5

Adj6

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="align: center"]Y[/TD]

[TD="align: center"]Y[/TD]

[TD="align: center"]Y[/TD]

[TD="align: center"]Y[/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"]900.88[/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>

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?