Finding Max and its corresponding min

[kamoorthy]

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Overs[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Runs[/TD]
[TD]10[/TD]
[TD]100[/TD]
[TD]7[/TD]
[TD]123[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]Wickets[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]
I want get the max value from "Wickets" and corresponding min value from "Runs". For ex, in this table Max value in "Wickets" is 4. I want to find the corresponding min value for 4. So i want my result as 4 and 56(instead of 100 and 123 in runs). I can use the Max function to get the value from A, but my question how i have to find the corresponding minimum value? And i want my result as 4-56

=CONCATENATE(MAX(OFFSET(SD!$C$4:$MM$4,(ROWS($1:2)-1)*4,0)),"-",MIN(IF(SD!$C$4:$MM$4=MAX(OFFSET(SD!$C$4:$MM$4,(ROWS($1:2)-1)*4,0)),SD!$C$3:$MM$3)))

I used this formula and Commit by using CTRL+SHIFT+ENTER and not just Enter by itself.

 

[Snakehips]

kamoorthy,

Maybe try.....

Excel 2007
	B	C	D	E	F	G
2	Overs	3	4	1	5	6
3	Runs	10	100	7	123	56
4	Wickets	2	4	2	4	4
5
6
7	4-56
SD

Cell Formulas
Range	Formula
B7	{=MAX(SD!C4:MM4)&"-"&MIN(IF(SD!C4:MM4=MAX(SD!C4:MM4),1,9^4)*(IF(SD!C4:MM4+C3:MM3=0,9^4,SD!C3:MM3)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Hope that helps.

 

[kamoorthy]

kamoorthy,

Maybe try.....
Excel 2007

	B	C	D	E	F	G
Overs
Runs
Wickets
4-56

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]

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

[TD="align: right"]10[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]56[/TD]

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

[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

SD​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B7[/TH]
[TD="align: left"]{=MAX(SD!C4:MM4)&"-"&MIN(IF(SD!C4:MM4=MAX(SD!C4:MM4),1,9^4)*(IF(SD!C4:MM4+C3:MM3=0,9^4,SD!C3:MM3)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Hope that helps.

Hello,

Thanks for your time. It works good for the first cell

1. {=MAX(SD!C4:MM4)&"-"&MIN(IF(SD!C4:MM4=MAX(SD!C4:MM4),1,9^4)*(IF(SD!C4:MM4+SD!C3:MM3=0,9^4,SD!C3:MM3)))}
2. {=MAX(SD!C5:MM5)&"-"&MIN(IF(SD!C5:MM5=MAX(SD!C5:MM5),1,9^4)*(IF(SD!C5:MM5+SD!C4:MM4=0,9^4,SD!C4:MM4)))}
3. {=MAX(SD!C6:MM6)&"-"&MIN(IF(SD!C6:MM6=MAX(SD!C6:MM6),1,9^4)*(IF(SD!C6:MM6+SD!C5:MM5=0,9^4,SD!C5:MM5)))}

FOr each cell, the formula looks like above. But i need to increment 4 cells automatically. After C4 i want to check with C8, then C12 and so on.

Please let me know, if i am missing anything

 

[Snakehips]

It depends on the data set and where you are putting the formula.
Something like below perhaps will drag down to give you what you want.

Excel 2007
	A	B	C	D	E	F	G
2		Overs	3	4	1	5	6
3		Runs	10	100	7	123	56
4		Wickets	2	4	2	4	4
5	4-56
6		Overs	1	2	3	4	5
7		Runs	34	22	7	123	100
8		Wickets	2	4	2	5	5
9	5-100
10		Overs	3	4	1	5	6
11		Runs	10	100	7	123	56
12		Wickets	2	4	2	6	4
13	6-123
SD

Cell Formulas
Range	Formula
A5	{=IF(B4="Wickets",MAX(SD!C4:MM4)&"-"&MIN(IF(SD!C4:MM4=MAX(SD!C4:MM4),1,9^4)*(IF(SD!C4:MM4+C3:MM3=0,9^4,SD!C3:MM3))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.