How to calculate best bowling figure (cricket)

[ahmedleo414]

Thanks, Chidambaram3983, for adding your alternatives.
Just for comparison, I have added them to my file

Alan

I have actually found a much simpler way to do it.

=MINIFS(D2:D21,E2:E21,MAX(E2:E21))&"/"&MAX(E2:E21)

no need for Macros, this does exactly what I was looking for

[DocAElstein]

Thanks a lot for coming back and adding another solution.
..."the more the merrier" ...
In Excel often ..."there's more than one way to skin a cat."

It's very helpful when learning to see alternatives, IMO

Alan

( BTW the first solution from Chidambaram3983 was a formula similar to yours, I think. But I am not too clued up on formula solutions. It looks similar at first glance

Often you can use a UDF ( User Defined Function) with your formula, or a similar one, in it to simplify things further as it typically reduces what you have in the spreadsheet.
( Although in the case of the UDF given to you by Chidambaram3983, he did a seperate alternative solution. ) )

[Pinch hitter]

Is it possible to pick best bowling figure, if it like 3/45, 4/40, 4/34 ... without separate section for wicket and runs...

[DocAElstein]

Hi Pinch hitter,
Welcome to ExcelFox
I can’t remember much about this Thread, and still I don’t know too much about cricket either.

The simple answer is that most likely in Excel with a formula or VBA we could manipulate data to get any result you want. Most likely there would be almost an infinite ways to do it as well.
To give you a specific solution or more detailed answer attempt I would need a small sample of your data and an example of the result you want to get.

(Or, if an answer here is close to what you want, (coding or formula), then tell me which one, and tell me what you want differently.)
Alan

[ahmedleo414]

I've got a new problem I need to figure out but can't quite do it.

Now I'm trying to calculate the highest score for a player. Here is an

Screenshot 2023-11-25 221209.jpg

Here is the code

Column N

=IF(AND(MAXIFS($C$2:C2,$A$2:A2,A2)=C2,H2=1),MAXIFS($C$2:C2,$A$2:A2,A2)&"*",MAXIFS($C$2:C2,$A$2:A2,A2))

Now the issue is, I need to have the * next to the high score if they player was Not Out (column H =1)

ODI Bat.xlsx

[DocAElstein]

Hi there, welcome back!

I won’t be able to help here, as I still mostly do VBA stuff. Formulas which I think you are interested in are not really my thing, especially things like MAXIFS , since they are only available in Excel since 2019. I tend personally to favour older stable Excel versions. (Bare in mind that if you figure out a solution using MAXIFS, then you can only use it in Excel versions of 2019 + )

Alan

[p45cal]

You seem to have done it in the ODI Bat.xlsx file you attached. Is there something missing?
One problem may be that you'll want to use those high scores as numbers (to do calculations or something else numeric) and those with the asterisk can't be; they're text.
In the attached I added a column O (headed High Score b) that is a shorter version of your formula, although all values in that column are text.
I also added a column P (headed High Score c). All these values can be treated as numbers. I used conditional formatting to add an asterisk if column H contained a 1.
What version of Excel are you using?

[explorerather]

I've provided the template file for your reference.

The concept is to identify the maximum value in column E (wickets) for a given player's statistics. If a player has only one match where they took 3 wickets (for instance, the highest in our example), we would then look at the corresponding value in column D of that same row to determine the number of runs allowed by the player. In this example, it would be 70. Consequently, the results for the best bowling figure would be represented as 70-3.

In another scenario, if a player has taken 3 wickets in two matches, we would seek the minimum value in column D. In the attached picture, the minimum is 32. Therefore, in this case, the results for the best bowling figure would be displayed as 32-3.

For your convenience, I have highlighted on sheet 3 where these results should be displayed.

[DocAElstein]

Thanks, explorerather, for adding a solutions, and welcome to ExcelFox

Just one thing…..

I've provided the template file for your reference……..

I don’t see any file anywhere??




Alan

[explorerather]

Hello,
Calculating the best bowling figures in cricket involves understanding and analyzing a bowler's performance in a particular match. The bowling figures are usually represented as overs, maidens, runs conceded, and wickets taken.
Thank you.