Maximums, minimums and everything in between

[mzcalav]

i am doing cricket statistics for my team. I want to be able to show the best bowling figures. I have wickets in one column and runs in the next column. best bowling figures is the one with the most wickets with the least amount of runs. i am able to find the most wickets by using the MAX function. i then used VLOOKUP to get the corresponding runs. My problem is that if i have more than one occasion with the most wickets, i need to match it to the one with the least amount of runs conceeded.

Any help would be much appreciated

 

[Fergus]

Which is better 9 wickets for 45 runs or 2 wickets for 1 run? If the latter is better then you can't just first find the maximum number of wickets. What about finding the MAX of wickets/runs?

 

[JohnnyTightlips]

in cricket, wickets is more important.

E.g. 9 for 150 runs is 'better' then 5 for 1 run

That should make a solution possible, but it's a tricky one. There's some experts on this forum though!

 

[PaddyD]

"in cricket, wickets is more important"

not necessarily:

http://www.lgiccrankings.com/about-rankings.php

..e.g. economy rewarded for ODI rankings.

 

[JohnnyTightlips]

not necessarily

touché; traditionally however, a bowler's "best bowling" figures refers to the most amount of wickets they have taken, for the least amount of runs. I'm not up to speed with these new fangled ways of measuring performance

Anyway, I had a play around with the formula given my interest in cricket, and determined a workable solution.

You are not clear exactly what you want to show. This formula will provide the lowest number of runs in there is more than one occurance of the max number of wickets:

{=MIN(IF(WicketsColumn=MAX(WicketsColumn),RunsColumn))}

Needs to be entered with CTRL+Shift+Enter

So if a bowler twice takes 5 wickets in a innings: 5/84 and 5/23, this formula will return the number 23 to the cell

Alternatively, if you want to identify the bowler with the best figures, this formula will work

={INDEX(BowlerNameColumn,MATCH(MIN(IF(WicketsColumn=MAX(WicketsColumn),RunsColumn)),RunsColumn,0))

Also needs Ctrl+Shift+Enter

This will return the bowler who had the best figures for a season

Good luck!

 

[mzcalav]

Cheers

Thanks johnny, exactly what i needed, Cheers