How to count cricket overs?

Hii,

Request to help me with a formula for counting overs in a cricket match.

If 8 balls are delivered it should show as 1.2 overs.

NO & WIDE should not be counted.

Sample excel file attached.

Regards
Tushar

8 balls would be 1.33 overs.

=balls delivered/6

Thanks Jason,

for your reply.

the given equation shows (8/1.33=6.02)
it should show as 1.2.

Can you pls suggest any other formula.

Regards
Tushar

Originally Posted by tushardnk

Thanks Jason,

for your reply.

the given equation shows (8/1.33=6.02)
it should show as 1.2.

Can you pls suggest any other formula.

Regards
Tushar

I suggested =balls delivered/6 which = 1.33, you have the formula backwards.

If you think that the result should be 1.2 could you please explain how you get from 8 to 1.2 when an over consists of 6 'legal' balls?

Please note, excel I understand, cricket I do not understand, I based my suggestion on information I found on google.

you can use MOD function.

=CONCATENATE((A1-B1-C1-MOD(A1-B1-C1,6))/6,".",MOD(A1-B1-C1,6))

Where A1 is number of total balls balled, b1 and c1 are no and wide balls.

Ok Jason,

In cricket after 6 'legal' balls it is counted s 1 over, therefore after 8 legal balls it should be 1.2 overs.

Hope this info may help you.

Ok Jason,

In cricket after 6 'legal' balls it is counted s 1 over, therefore after 8 legal balls it should be 1.2 overs.

Hope this info may help you.

So you want Overs and balls expressed as decimal, not an actual decimal value?

For example, no overs and 5 balls would be 0.5

Hii Good Evening,

You can have a look at the attachment, you shall come to know in details.

yes Jason, correct.

16 balls as 2.4 overs

Ok, I can't see how you arrive at 16 from your example though. The numbers in G6 to I13 add up to 26.

Try this one

Formula:

=INT(SUM($G$6:$I$13)/6)+(MOD(SUM($G$6:$I$13),6)/10)

Originally Posted by jason.b75

Ok, I can't see how you arrive at 16 from your example though. The numbers in G6 to I13 add up to 26.

Try this one

Formula:

=INT(SUM($G$6:$I$13)/6)+(MOD(SUM($G$6:$I$13),6)/10)

I have checked the file. Actually you are going down. See bowler name, every bowler has six cells in front of it. every cell is representing one ball.
Six balls equal to One over
so when six balls have been bowled the score board should turn to 1, 2 or 3 and so on. Similarly, 21 balls mean 3 complete overs and 3 balls, which should be written as 3.3 instead of 3.5 (which, in cricket means 3 overs and five balls)

I have a clue but that will change after 30 overs.
0.66 = one ball
but if you keep continuing with that the 0.02 difference will make a one ball error after 25 overs where 25 x 0.02 will become 0.5 and it will automatically round offed to 1 ball.

I hope I have defined it in detail

I want Overs and balls expressed as decimal, not an actual decimal value?

For example, no overs and 5 balls would be 0.5, after 0.6 balls it shd show as 1 over, then after one more ball it should show as 1.1, 1.2,1.3,1.4,1.5,2,2.1,2.2, continue. It shd not count "NO & WIDE".

That is exactly what the formula does.

Good Morning Jason

This formula shows the total value of all cells when we enter further. I need to count as one over of 0.6 balls. The result on these ball may be 1 run, 3 runs, 2 runs, 4 runs, OUT, 6 runs. This shd be count as 0.6 ball which should b shown as 1 over. After 0.7 ball it shd show as 1.1 over & continue when it is 1.6 balls (1 over & 0.6 balls) it shd show as 2 overs.

As far as I can see, the formula does what you are asking for.

There is nothing that tells me what the numbers in G6 to I13 mean so I have assumed that they are all balls delivered, a total of 26 balls, or 4.2 overs which is the result that I get from the formula.

If some of those numbers are not balls bowled then you need to tell us what each of the numbers refers to.

Hi , I have attached file regarding ur issue

Hope this will work

Sample sheet (2).xlsx

Hello

I checked the formula =INT(COUNT($G$6:$I$13)/6)+(MOD(COUNT($G$6:$I$13),6)/10)

But it does not count WKT as a ball. What can be added to count WKT as legal ball in the over?

If I enter 1 in a cell it shows as 01. in overs, but when I enter 2 in other cell it shows as 0.3 in overs , further if I enter 4 in next cell it shows as 1.1 in over cell??

If I enter 1 in a cell it shows as 0.1 in overs, but when I enter 2 in other cell it shows as 0.3 in overs , further if I enter 4 in next cell it shows as 1.1 in over cell??

Originally Posted by tushardnk

If I enter 1 in a cell it shows as 0.1 in overs, but when I enter 2 in other cell it shows as 0.3 in overs , further if I enter 4 in next cell it shows as 1.1 in over cell??

1 + 2 = 3 or 0.3 overs.
1 + 2 + 4 = 7 or 1.1 overs.

The formula is correct based on the information you have provided.

Thanks Glenn & Jacob,

Good Morning

Glenn the formula works only for numbers it does not count the ball on which wicket is fallen "WKT".

It should count the cell in which WKT in entered.

Originally Posted by tushardnk

the formula works only for numbers it does not count the ball on which wicket is fallen "WKT".

It should count the cell in which WKT in entered.

People have provided you with things that work based on what you have told us, and now you decide to give more information that you should have provided in your first post.

I'm done wasting my time here.

Sorry for the inconvenience caused.
The below formula given counts all the cells, but if we enter "WKT" in any cell.
The cell is not counted.

=INT(COUNT(B3:D8)/6)&"."&MOD(COUNT(B3:D8),6)

You can also refer Sheet2 of sample sheet "F2".

GREAT THANKS TO Mr. JASON & Mr. GLENN FOR SUPPORTING ME & GIVING YOUR VALUABLE TIME.

THE FORMULA IS WORKING NOW.

Glenn & Jason, I shall b needing support for one more post.
It shall be posted shortly.
See you on that post.

Bye & Regards
Tushar

Hey all, I'm looking to find an overs formula with just 2 different cells. Just say one cell (B3) says 1.4 and the other (B41) says 1.4. I'm looking for it to add up to 3.2 Overs.

I have a formula with this =INT(SUMPRODUCT(F87:F92*10-INT(F87:F92)*4)/6)+MOD(SUMPRODUCT(F87:F92*10-INT(F87:F92)*4),6)/10

So with this formula I can only add more than 2 cells, but I only want to add 2 separate cells with the right out come. For some reason I just can't do it. I'm new to the MOD and INT functions, and I've tried taking out SUMPRODUCT. I only want to use + instead of :

Thanks.

Originally Posted by Pajja

Hey all, I'm looking to find an overs formula with just 2 different cells. Just say one cell (B3) says 1.4 and the other (B41) says 1.4. I'm looking for it to add up to 3.2 Overs.

I have a formula with this =INT(SUMPRODUCT(F87:F92*10-INT(F87:F92)*4)/6)+MOD(SUMPRODUCT(F87:F92*10-INT(F87:F92)*4),6)/10

So with this formula I can only add more than 2 cells, but I only want to add 2 separate cells with the right out come. For some reason I just can't do it. I'm new to the MOD and INT functions, and I've tried taking out SUMPRODUCT. I only want to use + instead of :

Thanks.

change the value of one ball equal to 0.66

Originally Posted by FDibbins

Perhaps you did not notice, but that post was 6 years old?

I knew it and knowingly sent message for future readers to work on some project.

Hey Ali, that's ok. Call me stupid but I can not find how to create a new thread. I don't know if it's because I'm new on here or what. I just really need help for my cricket formula. Thanks.

Answer is not- satisfied
.

I am looking for the formula to convert the number of bowled balls into overs.
Unfortunately all the formulas shown do not work.
Who can help me with this, Thank you in advance

Example: bowler bowled 99 balls, how can I convert this into 16.3 overs

Greetz Arthur