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