Calculating players overs for cricket (average)

Not everyone understands cricket, so it would be very beneficial if you provided a sample worksheet with 8-15 records and showed a mocked up solution. Then with the example explain the criteria for the results you are showing. We may not be cricket experts, but we do understand Excel.

Also, please update your excel version to show the particular version, ie. 2019,2021, O365. 2311 is not a known version of excel.

Need average from B3 to C3 but with only 6 balls an over. 29 divide 10 is 2.9 overs a game which is not the answer. Should be closer to 2.3-2.5

How do you calculate that?

Adjusting your formula to reflect the games shown in the sample:

Formula:

Please Login or Register  to view this content.

Note this is from B4, not B3 as indicated because B3 contains the total overs.

I might be missing something, but that formula seems to convert the data from one format to another, sum the result, and converting it back. I can't see how that's ever going to return anything other than 2.9 (=29/10).

Again, what is the calculation you use to get the result you expect? Be best if you break it down line by line.

Need average from B3 to C3 but with only 6 balls an over. 29 divide 10 is 2.9 overs a game which is not the answer. Should be closer to 2.3-2.5

Why? I don't understand your logic. It's simple maths (to me):

=((B3*6)/10)/6

=2.9

No idea how you arrive at 2.3-2.5 - please enlighten us. Are you expecting the decimal part to be between .0 and .5 only??? In other words, not a decimal, but a number of balls?

Give this a try:

=LET(o,((B3*6)/10)/6,ROUND(INT(o)+MOD(o,1)*6/10,1))

14 overs divided by 4 is 3, leaving 2 overs, each of 6 balls i.e. total of 12 balls: divide 12 by 4 =3 hence 3.3 "average".

Formula:

Please Login or Register  to view this content.

A1=Number of overs, B1=number of games

Finally I see how to calculate the average.
Thank you John.

This calculation can be made with this formula, where the last part after the point (0-5) is rounded down if the average number of balls is not a whole number.

Please enter the numbers of overs in column A and the numbers of games in column B and try in Column C and copy down:

Formula:

Please Login or Register  to view this content.

Try:

3.3 =DOLLARFR(14/4,6)
2.54 =DOLLARFR(29/10,6)
2.54 =DOLLARFR(B3/C3,6)
2.492 =DOLLARFR(SUM(B4:B13)/C3,6)
2.492 =DOLLARFR(SUM(B4:B13)/COUNTA(B4:B13),6)

@hans: succinct formula as usual .. welcome to cricket!

No welcome required - the Dutch know all about cricket: https://en.wikipedia.org/wiki/Nether...l_cricket_team

@Ali: I know the game is played in the Netherlands [since at least the 19th century] ,but not everyone follows it (like any other game) and hence understands the cricket "idiosyncrasies" !

It was tongue-in-cheek, hence the winking smilie.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

Originally Posted by JohnTopley

@hans: succinct formula as usual .. welcome to cricket!

Succinct? Possibly. Working? Definitely not.

Overs 4.4 Games 2 gives an average using the formula you quoted as 2.1

4.4 overs is 28 balls. 28 balls over 2 games is 14 balls per game, which is 2.2 overs, not 2.1

Originally Posted by GameChanger

Only one works.

@Gamechanger, your solution in Post #5 returns exact the same results as my solution in Post #9.
So in your eyes, your own solution can't be right.