# How to Calculate the Average Score in Excel (7 Methods)

## 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.

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

Get FREE Advanced Excel Exercises with Solutions!

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.