Thursday 11 June 2015

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.