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: Convert Centimeters and Meters to Feet and Inches

Microsoft Excel does not have a built-in function to convert any measurement of length into inches and feet with single and double quote. Therefore, I have following formula that can convert any length specified in centimeters into feet and inches of x' x" format:

=TRUNC(B3/2.54/12)&"' "&TRUNC(MOD(B3/2.54,12))&""""

B3 is the address of cell containing centimeters.

Practical example:

Similarly, to convert meters into feet and inches of x' x" format use following formula:

=TRUNC(B8*100/2.54/12)&"' "&TRUNC(MOD(B8*100/2.54,12))&""""

B8 is the address of cell containing meters.

Practical example:

Warning: There are two disadvantages of above two formulas. First, results (6' 1" & 5' 3") are in text format rather than numerical format, therefore, they cannot be used in further calculations. Second, for simplicity sake, fractions of inches are simply dropped, not rounded off, but dropped.

Following formulas will round off inches to the nearest integer:

cm to ft' in"

=TRUNC(B3/2.54/12)&"' "&ROUND(MOD(B3/2.54,12),0)&""""

m to ft' in"

=TRUNC(B8*100/2.54/12)&"' "&ROUND(MOD(B8*100/2.54,12),0)&""""

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.

Microsoft Excel: Sum of Cricket Runs with Not Out Asterisk

If you are trying to add runs of individual batsmen of a cricket team using Microsoft Excel, you soon realize that the software skips the score with asterisk (example: 32*) indicating not out. The reason behind this is that Excel treats the value of cell containing a number and asterisk as text, therefore, does not include it in SUM function.

Following formula can solve this problem:

=SUM(B2:B9)+LEFT(B10,LEN(B10)-1)

B2 is the cell containing runs of first batsman and B9 contains the score of second last batsman. B10 contains the score of last batsman who is not out. This cell has asterisk with a numeric value.

Here is an example of this formula in action:

The total of 174 was acquired by using above formula.

Warning: For a scorecard where all batsmen are out use following simple formula:

 =SUM(B2:B10)

Using this simple formula in above example will result in false total of 171.

Let us take a look at more complex situation on a score card. This is most common one. At the end of a match we usually have two batsmen who are not out, hence, have asterisks with their runs. For such a situation, use following formula to add runs of all batsmen:

=SUM(IF(RIGHT(B2:B10,1)="*",LEFT(B2:B10,LEN(B2:B10)-1)*1,B2:B10))

Remember, this is not a simple formula. It is a special kind of formula called array formula. Here is how you use it.

B2 is the first cell in the range containing list of scores and B10 is the last. As always, you'll have to change it to the range of cells that you are using. After you have typed or copied this formula in your worksheet press CTRL+SHIFT+ENTER to calculate. In the formula bar, the formula will look like this:

{=SUM(IF(RIGHT(B2:B10,1)="*",LEFT(B2:B10,LEN(B2:B10)-1)*1,B2:B10))}

Important: Every time you edit this formula, use CTRL+SHIFT+ENTER to update the results.

Here are two examples of this formula in action:

Example 1:

Notice that the positions of two not out batsmen don't affect the calculation.

Example 2:

Here B2:B10 was used as range. Notice that blank cells also don't affect the calculation.

Cash Profit Rates (percentages) of National Savings

Whenever you invest your money in any of investment schemes in National Savings to get cash profit on regular basis, you never know until the completion of entire (and rather lengthy paperwork) how much cash you are actually going to take home.

General (R) Pervez Musharraf's government regularly and drastically reduced National Savings profit rates and, even now, you can never say for sure how much income tax you will be slapped with.

However, people at National Savings are kind enough that they will give you a clear figure of percentage of income tax if you ask them. Nevertheless, the percentage of profit they tell you, although technically true, is always somewhat misleading as to how much actual cash you'll be able to feel in your pocket.

After doing a little elementary algebra based on my experience with National Savings, I have come up with an equation which can solve a big mystery in a matter of minutes.

Mystery: What percentage of your amount you actually get in cash profit in any regular income scheme at National Savings?

And the answer is:


In this equation, x is the actual, working percentage of cash profit you are taking home. It is useful, when you already are taking home some amount of cash from an investment scheme, and you want to calculate how much money you could make on a different amount in same investment.

Here's how to use above equation. Let's start with variables.

x is percentage of cash you are currently taking home. National Savings never tells you this percentage. Your goal is to find the value of x.

P is the amount of cash you receive in profit from National Savings on an your investment.

y is number of times you receive the profit every year. It is generally a number from 1 to 12.

a is the amount of money that you have invested.

t is the percentage of income tax on your profit. This you have to ask National Savings by simply asking, "what is the percentage of income tax?"

By knowing P, y, a and t, you can calculate x, the percentage of cash profit that you get on your investment amount. From this percentage you can calculate possibilities of cash profits on different amounts in the same scheme without going through all banking mumbo jumbo of National Savings.

Using this equation in Microsoft Excel

Above equation is for pen and paper only. This equation may not give you proper answer in Microsoft Excel if you are not familiar with how the software works with percentages.

To use this equation in MS Excel, change into following:


In Excel, this equation will be entered as following:

=(100*B1*B2)/(B3*(100-B4))%

Where P = B1, y = B2, a = B3, t = B4

It is because of % sign in Excel's equation that we have to change 10000 to 100. If you want to use the original equation in Excel, then do not use % sign.

Microsoft Excel: Overnight shift hours calculation

Microsoft Excel can be used for your work record to keep track of number of hours worked in each shift. It is useful for those people who don't work regular day jobs and have to work at odd hours and/or have to work irregular lengths of shifts. However, it is not always easy to calculate the hours of day shifts and over-night shifts using the same formula. Here is formula which will accurately calculate lenghth of any shift which is not longer than 24 hours whether it is a day shift or over-night shift:

=IF(((B2-A2)*24)<0,(b2-a2)*24+24,(b2-a2)*24)

B2 and A2 are the addresses of two cells. Make sure that any cell replacing B2 contains the time of shift's end and one replacing A2 contains shift's start time.

Here are two examples in work:

Example 1:

Example with military style time

Example 2:

Example with AM/PM style time

The column with Hours heading contains the above mentioned formula. Please be sure to change the format of this column to 2-place decimal number.