Selecting the correct one from two or more seemingly duplicate MAX values

[Rich N]

I have a spreadsheet I update on an ongoing basis for my cricket team.

One of the values that this spreadsheet has to return is a bowler's best bowling figures. (Most wickets then, if two or more duplicate figures are returned, the bowling analysis that contains the most wickets but with the fewest number of runs conceded.

So, non-cricketers, 8 (wickets) for 49 (runs) is better than 7 for 12 and six for 16. But 8 (wickets) for 49 (runs) is inferior to 8 for 36.

The formula I had previously worked perfectly (the values were listed in columns).

For selecting the most wickets: =MAX(FF2:FF24)

For selecting the fewest runs to go with most wickets: {=MIN(IF(FF2:FF24=MAX(FF2:FF24),IF(NOT(ISBLANK(FH2:FH24)),FH2:FH24)))} {ARRAY FORMULA}

After 25 years of compiling stats for my cricket club, I wanted to tweak the spreadsheet to make it easier to maintain on an ongoing basis. This meant that instead of looking down a column for these values, I now want it to look along the row, season by season. (The reason for this - we have used almost 200 bowlers, and I want the bowlers to be listed vertically and the years horizontally, rather than the other way round as they are currently).

There are blocks of three columns, the first is the wickets, the second simply the word "for" and the third the number of runs. Eg 8 for 49.

Amending the formula to return to find the most wickets was not a problem, simply looking along every third cell in the row (inelegant, admittedly, I am sure there must be a better way, but as our 25th anniversary dinner is on Saturday night needs must!!)

The problem is with returning the value of the correct number of runs to go alongside the best bowling, particularly with duplicate values in terms of wickets.

You're all going to laugh at this, but I amended my second array formula above and replaced it with the appropriate cell values, one by one (you will see it below). I guess, not surprisingly, that I get a message saying that I have entered too many arguments for this function. A three-in-one question please:

(i) is my formula below incorrect, or just too long?
(ii) is there a way of fixing it (of course there is, but not by thicko here!)
(iii) if (ii) is possible, is there then a more elegant way of fixing it to save on characters?

Very sorry, but I suffer from macrophobia, so nothing that involves the dreaded "M" word, please!

Here's the offending (probably offends your eyes too!) array formula:

{=MIN(IF(JP2,JS2,JV2,JY2,KB2,KE2,KH2,KK2,KN2,KQ2,KT2,KW2,KZ2,LC2,LF2,LI2,LL2,LO2,LR2,LU2,LX2,MA2,MD2,MG2,MJ2=MAX(JP2,JS2,JV2,JY2,KB2,KE2,KH2,KK2,KN2,KQ2,KT2,KW2,KZ2,LC2,LF2,LI2,LL2,LO2,LR2,LU2,LX2,MA2,MD2,MG2,MJ2),IF(NOT(ISBLANK(JR2,JU2,JX2,KA2,KD2,KG2,KJ2,KM2,KP2,KS2,KV2,KY2,LB2,LE2,LH2,LK2,LN2,LQ2,LT2,LW2,LZ2,MC2,MF2,MI2,ML2)),JR2,JU2,JX2,KA2,KD2,KG2,KJ2,KM2,KP2,KS2,KV2,KY2,LB2,LE2,LH2,LK2,LN2,LQ2,LT2,LW2,LZ2,MC2,MF2,MI2,ML2)))}

P.S. Peter_SSs, I still see 10 years after helping me enormously you are still going strong. What a trooper!

 

[Gerald Higgins]

Have you considered a different approach ?

If you consolidated the wickets and runs into a single number, so for example 8 wickets for 49 runs would become 8.49 ?

This would mean that someone with 8 for 49 would appear to be better than someone who scored 7 for 99 (or whatever) which is correct.
The problem is that someone with 8 for 49 would appear to be WORSE than someone who scored 8 wickets for 99, which is wrong.
You could solve this by using the reciprocal of the runs figure.

8 wickets, 2 runs = 8+1/2 = 8.5
8 wickets, 49 runs = 8+1/49 = 8.0204
8 wickets, 99 runs = 8+1/99 = 8.0101

It's then simple to rank the bowlers on these new scores.
You could hide this data if you didn't want to display it.
One problem, this basic formula would give you problems if the runs were 0 or 1, but you could tweak the formula to deal with those possibilities.

 

[Rich N]

Thank you for your swift reply Gerald.

That's given me food for thought, certainly, not something I had considered.

I'm currently churning out pages of stats so I might not be able to come back and offer a "eureka, sorted" moment immediately, but I really appreciate the input and will definitely have a tinker and report back in due course.