1

Hoping someone can help me!

I've worked myself out a cricket scoring spreadsheet, which works absolutely fine - trying to sort out a summary page, where you see (funnily enough!) a summary of the game info.

As part of this, I'd like to be able to use the information on the following table:

Bowler  Wkts    Runs
 Amir     0     41

 Irfan    1     46

 Sami     2     23

 Imad     0     26

 Afridi   2     40

to select the best bowler for the summary page.

I have an understanding of writing an INDEX...MATCH... statement, but am having trouble with this, because I would like to select the bowler name where the wickets value is the most, but the runs value is the lowest amongst the bowlers taking that number of wickets - in this case, Sami.

I imagine I'll be trying something using INDEX, MATCH, MAX and MIN (with some IFs thrown in!), but really don't know where to start.

Any help would be gratefully received!

0
=INDEX(A2:A6,MATCH(MIN(IF(B2:B6=MAX(B2:B6),C2:C6,9E+109)),C2:C6,0))

Confirm as an array formula with ctrl+shift+enter.

MIN(IF(B2:B6=MAX(B2:B6),C2:C6,9E+109)) Finds the bowlers with the highest number of wickets and returns their number of runs. For everyone else, it simply returns an unrealistically high number (9E+109).

MATCH(MIN(...,C2:C6,0)

Uses match() and min() to find the lowest number of runs amongst the bowlers we previously identified.

INDEX(A2:A6,...)

Returns the actual bowlers name.

  • Thanks for this, Kyle - as Norman Stanley Fletcher would have said, "it worked like a bleedin' charm!". Is there any way this could be extended to show the second and third best bowlers? – Dave Coram Mar 23 '16 at 16:21
  • @DaveCoram Not easily. The second and third best bowlers in your example are obvious, but what if there was another bowler with 1 wicket? In order to find the third best bowler, we'd have to find the second most wickets, and the least runs with that amount of wickets. You'd be better off with a VBA solution. – Kyle Mar 23 '16 at 16:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.