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:

=TRUNC(J2,0)*6+RIGHT(J2,1)

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.

No comments: