Finding Max and its corresponding min

[kamoorthy]

Overs	3	4	1	5	6
Runs	10	100	7	123	56
Wickets	2	4	2	4	4

<tbody>
</tbody>

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

SD​

Array Formulas Cell 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)))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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.