Cricket overs

Musaddiq

New Member
Joined
Jul 15, 2012
Messages
6
Hi Sir,
i have one problem with formula Understanding

In I17 this formula is apply to get result of 35.5
=(I15-INT(I16))+IF(MOD(I16,1)=0,0,0.6-MOD(I16,1)-1)
Sir can you explain me how this formula is working to give the result of remaining balls..
HI
15total overs50
16Overs Balled14.1
17Remaining overs35.5

<TBODY>
</TBODY>
 
Last edited:

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
=(I15-INT(I16))+IF(MOD(I16,1)=0,0,0.6-MOD(I16,1)-1)

I15-INT(I16) = 36 (50 - 14)
MOD(I16,1)=0 is False (MOD(I16,1) = .1, not 0) Therefore:
+(0.6 -.1)-1 = -.05

36 + (-.5) = 35.5
 
Upvote 0
This is what it is saying

It takes the 50 overs and subracts a the over bowled rounded down to the nearest whole number(14). It will then add an IF statement. It will take the overs bowled 14.1 and divide by 1. if the the answer = 0, then it will at 0. If not, it will take 0.6 - overs bowled 14.1 and divide by 1, the sunbract 1. Once it has done that, it will at the fighres together to give answer.
 
Upvote 0
thank you sir for you help...
and sir can you give any simple formula for the calculate cricket overs???... so that i can apply easily...
 
Upvote 0
Sir actually i want any alternate formula for calculating the remaining over just like as above i describe you actually this formula "=(I15-INT(I16))+IF(MOD(I16,1)=0,0,0.6-MOD(I16,1)-1)" is too long as well as some complicated for me.... so is there any other alternate formula which is simple....?
 
Upvote 0
This is a 2 cell approach, you could combine it into 1 if you understand whats happening:

C5 contains number of overs bowled: Example: 30.5 overs bowled.

H5 converts the number of overs bowled into number of balls bowled with this formula:

=INT(C5)*6+MOD(C5,INT(C5))*10
= 185 balls bowled.

Understanding this formula:

Int function will return the closest Integer to the number in C5 (35.5). This will return 35. This is multiplied into 6 which gives 180 balls.

Now the .1 over here is tricky. First to obtain the .1 we use MOD.

MOD Function will return the "REMAINDER" after a number is divided by another. Example: MOD(5,2) = 1. (As 2*2 =4 and then 5-4) = 1

So using MOD we get = MOD(C5,int(C5)) , this will return .5 (Remember Int(C5) is 35 - So we are dividing 35.5 by 35 which gives us a remainder of .5 )

Now, as .5 in this case represents 5 ballsl multiply this by 10 = .5 * 10 = 5.

If the overs bowled were 35.2, the above sentence would be .2 * 10 = 2.

Now just add this number to the number of balls bowled. = 180 + 5 = 185

Using the same approach, the formula in K5 is =INT((300-H5)/6)+MOD((300-H5),6)/10

INT((300-H5)/6) will return the number of returning overs. (14 overs)

MOD((300-H5),6)/10 will return the number of remaining balls if an over is incomplete. (.1)

Hope this helps.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top