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.

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:

=INT(J2)*6+(J2-INT(J2))*10

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.

Microsoft Excel: Adding Overs in Cricket

Those of you who know the gentlemen's game are aware of the fact when talking about overs, the number after the decimal point always indicates balls that have been bowled. For example: 6.5 overs means 6 overs and 5 balls, 11.2 overs means 11 overs and 2 balls, and so on so forth.

Since each over has 6 balls, here is how mathematics of cricket overs works:

 

In first line, we are adding 1 ball to 6 overs and 5 balls resulting in 7 complete overs. In second line, we are adding 4 balls to 11 overs and 2 balls resulting in 12 complete overs. In third line - that's where things get a little interesting - we are adding 2 overs and 3 balls to 5 overs and 4 balls. Adding 3 and 4 gives us 7. Since an over cannot have more than 6 balls, we mentally subtract 6 from seven and get 1. We turn 6 balls - that we just subtracted - into 1 over and carry it over to other side of the decimal point and add it to 5 and 2. Using the example of elementary arithmetic we get:

This seems like a hybrid of base 10 and base 6 mathematics. Since base 6 is not built into Microsoft Excel, doing this kind of addition can be challenging, if not impossible, in a worksheet.

You can use following rather complex formula to add overs of multiple players in a score card:

=SUM(TRUNC(C2:C10,0))+TRUNC(SUM(C2:C10-TRUNC(C2:C10,0))*10/6)+MOD(SUM(C2:C10-TRUNC(C2:C10,0))*10,6)/10

C2:C10 is the range of cells that contains overs that you want to add. Here is this formula in action:

Another slightly modified example:

Remember: Above formula is of a special kind called array formula. After you have typed or copied this formula in your worksheet, press CTRL+SHIFT+ENTER to calculate. Every time you edit this formula, you will have to use same three keys to recalculate.