Microsoft Excel: Cricket: Convert Balls into Overs

In cricket, converting balls to overs is equally difficult as overs to balls when there are fractions of overs involved. If a given number of balls is a multiple of 6 (each over has 6 balls) a simple division is enough. If not, then you have to take the remainder and convert it into fraction part of resulting overs. For example, converting 19 balls to overs gives 3.1. Divide 19 by 6, answer is 3 and remainder is 1, hence, 3.1 overs.

Unfortunately, Microsoft Excel cannot handle this hybrid system of base 10 and base 6 mathematics that is the specialty of arithmetic of Cricket overs. To convert balls into overs in Excel use following formula:

=TRUNC(J17/6,0)+MOD(J17,6)/10

J17 is the address of cell containing balls. 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.