How to Calculate Average Score in Excel (7 Suitable Ways)

A common task in sports is to calculate the average score of a player. Microsoft Excel makes calculating the average score easy. Excel has a variety of formulas for calculating averages. The formulas we get to learn to calculate average scores will also be useful in our practical work. In this article, we will learn 7 suitable ways to calculate the average score in Excel. Letโ€™s get started!


Download Practice Workbook

You can download the Excel workbook from here.


7 Suitable Ways to Calculate Average Score in Excel

Letโ€™s first introduce our dataset. We have Mathew Wade as our batsman. Our goal is to calculate the average score of Mathew Wade for 5 matches. We will calculate the average score in excel by using the Arithmetic formula, the AVERAGE function, the AVERAGEA function, the AVERAGEIF function, combining SUM and COUNTA functions, inserting SUBTOTAL function, and combining AVERAGE and LARGE or SMALL functions.

Suitable Ways to Calculate Average Score in Excel


1. Applying Arithmetic Formula

Applying Arithmetic formula is an easy method to calculate the average score in Excel. We get the freedom to apply the formula we want in this method. Follow the steps below to calculate the average score of Mathew Wade by using Arithmetic formula.

๐Ÿ“Œ Steps:

  • Firstly, select cell C11 and type the following formula:
=(C6+C7+C8+C9+C10)/5
  • In addition, press Enter and it will calculate the average of 5 matches of Mathew Wade.

Here, (C6:C10) are the scores of Mathew Wade for 5 matches.

Suitable Ways to Calculate Average Score in Excel


2. Use of AVERAGE Function

For calculating the average, the AVERAGE function is the simplest. Whenever there are no criteria, we can use it to find the direct average. Follow the steps below to apply this function to calculate average score of Mathew Wade in excel.

๐Ÿ“Œ Steps:

  • First, select cell C11 and type the following formula:
=AVERAGE(C6:C10)
  • Next, press Enter and it will calculate the average of 5 matches of Mathew Wade.

Suitable Ways to Calculate Average Score in Excel


3. Utilizing AVERAGEA Function if Any Input Is Unavailable

If our selected argument cells contain text, we can use the AVERAGEA function. To calculate average score in excel, based on this definition, AVERAGEA function evaluates text as zero, logical values TRUE as 1, and logical values FALSE as zero. To demonstrate how AVERAGEA function works, we have changed the run of Match 2 to Not Played.

Follow the steps below to apply this function to calculate average score of Mathew Wade.

๐Ÿ“Œ Steps:

  • To begin with, select cell C11 and type the following formula:
=AVERAGEA(C6:C10)
  • After that, press Enter and it will calculate the average of 5 matches of Mathew Wade.

Suitable Ways to Calculate Average Score in Excel


4. Inserting AVERAGEIF Function to Calculate Conditional Average Run

The AVERAGEIF function calculates the arithmetic mean of cells that satisfy particular criteria within the range provided. To demonstrate how the AVERAGEIF function works, we have changed the match types as ODI, Test and T20 in the dataset. Now, our goal is to calculate average score of Mathew Wade for ODI matches only by using the AVERAGEIF functionย in excel.

Follow the steps below to apply this function to calculate average score of Mathew Wade in ODI matches.

๐Ÿ“Œ Steps:

  • First of all, select cell C11 and type the following formula:
=AVERAGEIF(B6:B10,"ODI",C6:C10)
  • Finally, press Enter and it will calculate the average of the ODI matches of Mathew Wade.

Suitable Ways to Calculate Average Score in Excel

Read More: How to Make Cricket Scorecard in Excel (With Easy Steps)


5. Using Combination of SUM and COUNTA Functions

Another easy way to calculate the average score in Excel is to combine the SUM function and the COUNTA function. Follow the steps below to apply this method to calculate average score in excel of Mathew Wade for 5 matches.

๐Ÿ“Œ Steps:

  • Firstly, select cell C11 and type the following formula:
=SUM(C6:C10)/COUNTA(C6:C10)
  • Secondly, press Enter and it will calculate the average of 5 matches of Mathew Wade.

๐Ÿ”Ž How Does the Formula Work?

  • SUM(C6:C10): Firstly, the SUM function first calculates the summation from cells C5 to C14, which we have written in the formula.
  • COUNTA(C6:C10): The COUNTA function counts the argument numbers from cell C5 to C14.
  • SUM(C6:C10)/COUNTA(C6:C10): Then the result from the SUM function is divided by the result of the COUNTA function to compute the average.

6. Inserting SUBTOTAL Function

The SUBTOTAL function is one of Excelโ€™s most powerful functions. There are many ways in which we can use this function. Follow the steps below to apply this method to calculate average score in excel of Mathew Wade for 5 matches.

๐Ÿ“Œ Steps:

  • First, select cell C11 and type the following formula:
=SUBTOTAL(1,C6:C10)
  • Secondly, press Enter and it will calculate the average of 5 matches of Mathew Wade.

After taking SUBTOTAL in the formula, we can see different function names in the list. Among them, AVERAGE is the first one. Here, 1 refers to this AVERAGE function.

Suitable Ways to Calculate Average Score in Excel

Read More: How to Calculate Total Score in Excel (4 Easy Ways)


7. Combining AVERAGE and LARGE or SMALL Functions

We can also calculate the average score in excel of the highest or lowest 3 scores of a player by combining AVERAGE and LARGE or SMALL functions. This method gives us the freedom to be selective in our operation. Now we will see both examples to calculate the average in excel of the highest and lowest 3 scores of Mathew Wade.

7.1 Calculating Average of 3 Highest Runs

In order to calculate an average of the 3 highest runs of Mathew Wade, we need to combine the AVERAGE function and the LARGE function. Follow the steps below to apply this method to calculate the average of the 3 highest scores of Mathew Wade.

๐Ÿ“Œ Steps:

  • At first, select cell C11 and type the following formula:
=AVERAGE(LARGE(C6:C10,{1,2,3}))
  • Then, press Enter and it will calculate the average of the 3 highest runs of Mathew Wade.

๐Ÿ”Ž How Does the Formula Work?

  • LARGE(C6:C10,{1,2,3}): Here, the formula LARGE(C6:C10,{1,2,3})) will first return the largest 3 values from cell C6 to C10.
  • AVERAGE(LARGE(C6:C10,{1,2,3})): Then the AVERAGE function computes the average of the results of the first formula.

7.2 Calculating Average of 3 Lowest Runs

Now, to calculate average of 3 lowest runs of Mathew Wade, we need to combine the AVERAGE function and the SMALL function. Follow the steps below to apply this method to calculate average of 3 highest scores of Mathew Wade in excel.

๐Ÿ“Œ Steps:

  • To begin with, select cell C11 and type the following formula:
=AVERAGE(SMALL(C6:C10,{1,2,3}))
  • Then, press Enter and it will calculate the average of 3 lowest runs of Mathew Wade.

๐Ÿ”Ž How Does the Formula Work?

  • SMALL(C6:C10,{1,2,3}): Here, the formula SMALL(C6:C10,{1,2,3})) will first return the smallest 3 values from cell C6 to C10.
  • AVERAGE(SMALL(C6:C10,{1,2,3})): Then the AVERAGE function computes the average of the results of the first formula.

Things to Remember

  • If you have a large dataset, then using the Arithmetic formula will not be convenient for you as you will have to write a very long formula then.
  • If you want to calculate the average score with a condition, then using the AVERAGEIF function will be the best method for you.
  • To calculate the average of the highest or lowest 3 scores of a player, a combination of AVERAGE and LARGE or SMALL functions is the most effective one.

Conclusion

Hence, follow the above-described steps. Thus, you can easily learn how to calculate average score in Excel. Hope this will be helpful. So, follow the ExcelDemy website for more articles like this. Donโ€™t forget to drop your comments, suggestions, or queries in the comment section below.


Related Articles

 

Md. Asaduzzaman

Md. Asaduzzaman

Hello! I am Md. Asaduzzaman. Currently, I am working as an Excel and VBA Content Developer and I will be posting my articles related to this here. I graduated from Bangladesh University of Science and Technology(BUET) in 2022. I completed my BSc in Naval Architecture and Marine Engineering. I like to solve real-life problems in Microsoft Excel and share the solutions through articles. I post here regularly. Hope you find the articles helpful.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo