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!
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, AVERAGEA , AVERAGEIF functions, combining SUM and COUNTA functions, inserting SUBTOTAL function, and combining AVERAGE and LARGE or SMALL functions.
1. Using Arithmetic Formula to Calculate Average Score in Excel
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 to Calculate Average Score
For calculating the average, the AVERAGE function is the simplest. Whenever there are no criteria, we can use them to find the direct average. Follow the steps below to apply this function to calculate the 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.
3. Applying AVERAGEA Function to Calculate Average Score (If Cell Contains Text)
If our selected argument cells contain text, we can use the AVERAGEA function. To calculate the 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 the 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 the 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 the 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.
5. Combining SUM and COUNTA Functions to Calculate Average Score in Excel
Another easy way to calculate the average score in Excel is to combine the SUM and COUNTA functions. 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.
Read More: How to Calculate Quality Score in Excel
6. Using 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
7. Calculating Average Highs and Lows with the 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 the average of the 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 the average of the 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.
Download Practice Workbook
You can download the Excel workbook from here.
Conclusion
Hence, follow the above-described steps. Thus, you can easily learn how to calculate average score in Excel. Hope this will be helpful. 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