Maximum/Minimum poser

[Mel Smith]

I have the following data in these cells and I am trying to put a formula together which will give me the 'best' outcome. This is calculated by the best number in front of the "/" coupled with the lowest number after the "/"
the expected answer in this example is: 2\10. My formula, so far is:=MAX(DO3,DR3,DU3,DX3)&"/"&MIN(DP3,DS3,DV3,DY3) but as you can see, it gives me an incorrect answer. It's giving me the best in the 'front' number and the best in the 'back' number but not with them coupled as they are in the cell (in this case cell DW3.

Any help will be most appreciated.

Mel

Book1
	DN	DO	DP	DQ	DR	DS	DT	DU	DV	DW	DX	DY
1
2	Figures	Wickets	Runs	Figures	Wickets	Runs	Figures	Wickets	Runs	Figures	Wickets	Runs
3	2\11	2	11				1\9	1	9	2\10	2	10
4
First Class

Cell Formulas
Range	Formula
DN3	DN3	=IF(AY3="~~~","",AY3)
DO3	DO3	=IF(AY3="","",LEFT(AY3,FIND("\",AY3)-1)+0)
DP3	DP3	=IF(AY3="","",REPLACE(AY3,1,FIND("\",AY3),"")+0)
DQ3	DQ3	=IF(BK3="~~~","",BK3)
DR3	DR3	=IF(BK3="","",LEFT(BK3,FIND("\",BK3)-1)+0)
DS3	DS3	=IF(BK3="","",REPLACE(BK3,1,FIND("\",BK3),"")+0)
DT3	DT3	=IF(BW3="~~~","",BW3)
DU3	DU3	=IF(BW3="","",LEFT(BW3,FIND("\",BW3)-1)+0)
DV3	DV3	=IF(BW3="","",REPLACE(BW3,1,FIND("\",BW3),"")+0)
DW3	DW3	=IF(CI3="","",CI3)
DX3	DX3	=IF(CI3="","",LEFT(CI3,FIND("\",CI3)-1)+0)
DY3	DY3	=IF(CI3="","",REPLACE(CI3,1,FIND("\",CI3),"")+0)

 

[jasonb75]

See if this does what you need

=INDEX($DN$3:$DW$3,MATCH(AGGREGATE(14,6,DO3:DX3/DP3:DY3/($DO$2:$DX$2="Wickets")/($DP$2:$DY$2="Runs"),1),INDEX(DO3:DX3/DP3:DY3/($DO$2:$DX$2="Wickets")/($DP$2:$DY$2="Runs"),),0))

 

[Peter_SSs]

Does this work for you?

20 04 19.xlsm
	DN	DO	DP	DQ	DR	DS	DT	DU	DV	DW	DX	DY	DZ	EA
1
2	Figures	Wickets	Runs	Figures	Wickets	Runs	Figures	Wickets	Runs	Figures	Wickets	Runs		Best
3	2\11	2	11				1\9	1	9	2\10	2	10		2\10
Best Bowling

Cell Formulas
Range	Formula
EA3	EA3	=MAXIFS(DN3:DX3,DN$2:DX$2,"Wickets")&"\"&MINIFS(DO3:DY3,DO$2:DY$2,"Runs",DN3:DX3,MAXIFS(DN3:DX3,DN$2:DX$2,"Wickets"))

 

[Mel Smith]

Hello Peter,

Superb! Many thanks. I've never seen MAXIFS or MINIFS before. Time for a little bit of research...

Mel

 

[Mel Smith]

Many thanks, Jason. Peter’s formula worked a treat. I’ve never seen MAXIFS or MINIFS before...

Mel

 

[Peter_SSs]

Superb! Many thanks.

You're welcome.

I've never seen MAXIFS or MINIFS before.

They are reasonably new.