Wednesday, 24 June 2015

Microsoft Excel: Cricket: Convert Overs into Balls

In cricket, any digit after decimal point of a number representing overs is not fraction of overs but the balls of next incomplete over; for example: 3.2 overs means 3 overs and 2 balls. Converting 3 overs into balls with or without Microsoft Excel is simple. All you have to do is multiply 3 by 6 which would be 18 balls. To convert 3.2 overs into balls, first you multiply 3 by 6 to get 18, then add 2 to get 20. This can be a little challenging in Microsoft Excel too. To make it simple, use following formula:


J2 is the address of cell containing overs with or without fraction. Following illustrates an example of this formula in action:

In above example, each yellow highlighted cell contains aforementioned formula. Turquoise highlighted cell contains formula explained here. Runs were added using a formula detailed here.


Unknown said...

But 4.0 overs is taken as 28 balls by this method. What about that?

Anons said...

The formula has been revised to fix the error described above.