# Maximums, minimums and everything in between

#### mzcalav

##### New Member
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

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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?

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!

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:

Code:
``{=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

Code:
``={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!

Cheers

Thanks johnny, exactly what i needed, Cheers

Replies
10
Views
354
Replies
2
Views
232
Replies
9
Views
514
Replies
5
Views
164
Replies
2
Views
194

1,219,995
Messages
6,151,359
Members
451,022
Latest member
Baijano23

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back