Md. Asaduzzaman
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.
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.
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.
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.
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.
Read More: How to Make Cricket Scorecard in Excel (With Easy Steps)
Similar Readings
- Create Scorecard in Excel (with Detailed Steps)
- How to Calculate T Score in Excel (4 Easy Ways)
- How to Create a Scoring Matrix 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.
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.
Read More: How to Create a Scoring System in Excel (With Easy Steps)
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.
I fully understand this. But I’m trying this without the total of a game. I want to find the average per turn over multiple games. So I got a two-dimentional array with scores of multiple people and I want to find the average for player x. =AVERAGEIF(‘Quirkle scores’!$B$1:$ZZ$1; “*”&E2&”*”;’Quirkle scores’!$B$4:$ZZ$101) This is what I came up with so far. But I haven’t yet found an answer. So I’m looking in B1;ZZ1 for a name in E2. And than I want to average all those columns combined. But with AVERAGEIF it only looks at B4:ZZ4 now instead of B4:ZZ101. Would love to hear another insight.
Dear Wietze,
Thank you for your comment. unfortunately, your question seems unclear to me. If you can provide me with your Excel file and be more specific about your inquiry, I will be able to help you. Please share your Excel file.
Best,
Afia Aziz Kona