Maximums, minimums and everything in between

mzcalav

New Member
Joined
Sep 26, 2006
Messages
4
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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
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

Board Regular
Joined
Aug 13, 2006
Messages
94
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!
 

JohnnyTightlips

Board Regular
Joined
Aug 13, 2006
Messages
94
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 :biggrin:

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!
 

Forum statistics

Threads
1,141,626
Messages
5,707,489
Members
421,510
Latest member
haroonstr

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top