## Dataset Overview

Letâ€™s start by introducing our dataset. We have **Mathew Wade** as our batsman, and our goal is to calculate his **average score** for **5 matches**. Weâ€™ll explore different methods to achieve this using Excel functions.

## Method 1 – Using Arithmetic Formula

- Select cell
**C11**. - Type the following formula:

`=(C6+C7+C8+C9+C10)/5`

- Press
**Enter**to calculate the average score for*Mathew Wade*across 5 matches.

## Method 2 – AVERAGE Function

- Select cell
**C11**. - Type the formula:

`=AVERAGE(C6:C10)`

- Press
**Enter**to find the direct average of*Mathew Wadeâ€™s*scores.

## Method 3 – AVERAGEA Function (Handling Text Cells)

To demonstrate how **AVERAGEA **function works, we have changed the run of **Match 2** to **Not Played**.

- Select cell
**C11**. - Use the formula:

`=AVERAGEA(C6:C10)`

- Press
**Enter**to calculate the average, even if some cells contain**text**(evaluates text as zero,**TRUE**as**1**, and**FALSE**as**zero**).

## Method 4 – AVERAGEIF Function (Conditional Average)

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.

- Select cell
**C11**. - Apply the formula:

`=AVERAGEIF(B6:B10,"ODI",C6:C10)`

- Press
**Enter**to find the average score for*Mathew Wade*in ODI matches.

## Method 5 – Combining SUM and COUNTA Functions

- Select cell
**C11**. - Use this formula:

`=SUM(C6:C10)/COUNTA(C6:C10)`

- Press
**Enter**to calculate the average score by summing the scores and dividing by the count of non-empty cells.

** How Does the Formula Work?**

**SUM(C6:C10)**Â calculates the sum of Mathew Wadeâ€™s scores (from cells C6 to C10).**COUNTA(C6:C10)**counts the number of non-empty cells in the same range.- The result of the sum is divided by the count to compute the average.

## Method 6 – Using the SUBTOTAL Function

The SUBTOTAL function is a powerful tool in Excel. It allows us to perform various calculations within a specified range, while also considering hidden or filtered data. Hereâ€™s how to use it to calculate the average score for Mathew Wade across 5 matches:

- Select cell
**C11**. - Type the following formula:

`=SUBTOTAL(1,C6:C10)`

- Press
**Enter**to calculate the average of*Mathew Wadeâ€™s*scores.

In the formula, the number **1Â **corresponds to the **AVERAGE** function within the **SUBTOTAL**. This means it will calculate the average of the visible values in the specified range.

## Method 7 – Calculating Average Highs and Lows with the AVERAGE and LARGE or SMALL Functions

We can also determine the average of the highest or lowest scores using combinations of **AVERAGE** with either the **LARGE** or **SMALL** function. This approach provides flexibility. Letâ€™s explore both scenarios:

### 7.1 Calculating Average of 3 Highest Runs

To find the average of *Mathew Wadeâ€™s* highest 3 scores, follow these steps:

- Select cell
**C11**. - Use the formula:

`=AVERAGE(LARGE(C6:C10,{1,2,3}))`

- Press
**Enter**to calculate the average of the**3 highest runs**.

** How Does the Formula Work?**

**LARGE(C6:C10, {1, 2, 3})**returns the**three**largest values from cells**C6**to**C10**.- The
**AVERAGE**function then computes the average of these three values.

### 7.2 Calculating Average of 3 Lowest Runs

For the average of *Mathew Wadeâ€™*s lowest 3 scores, follow these steps:

- Select cell
**C11**. - Use the formula:

`=AVERAGE(SMALL(C6:C10,{1,2,3}))`

- Press
**Enter**to calculate the average of the**3 lowest runs**.

** How Does the Formula Work?**

**SMALL(C6:C10, {1, 2, 3})Â**returns the three smallest values from cells**C6**to**C10**.- The
**AVERAGE**function then computes the average of these three values.

## Things to Remember

- If youâ€™re dealing with a large dataset, avoid using the
**Arithmetic formula**(adding individual scores) due to its length. - When applying conditions, the
**AVERAGEIF**function is ideal. - For calculating averages based on
**highest**or**lowest**scores, combining**AVERAGE**with**LARGE**or**SMALL**functions is effective.

**Download Practice Workbook**

You can download the practice workbook from here:

**<< Go Back to Scoring | Formula List | Learn Excel**

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