## Introduction to Excel AVERAGE Function

The **AVERAGE **function is categorized under the ** Statistical **functions in Excel. This function returns the average value of a given argument.

**Summary:**

Returns the average (arithmetic mean) of its arguments, which can be numbers, names, arrays, or references that contain numbers.

**Syntax:**

`AVERAGE (number1, [number2], ...)`

**Arguments:**

Argument | Required/Optional | Explanation |
---|---|---|

number1 |
Required | This is the first number, cell reference, or range for which we want the average. |

number2 |
Optional | Additional numbers, cell references, or ranges for which you want the average, up to a maximum of 255. |

### Example 1 – Basic Use of the AVERAGE Function

- Suppose we have a simple dataset of five students and their scores in three tests. To find the average score for each student, insert the following formula in cell
**F5**:

`=AVERAGE(C5:E5)`

- Press
**Enter**. - Replace the
**cell references**as needed to find averages for other students. - Or use the
**Fill Handle**tool to copy the formula down to the other cells in column F.

**Use AVERAGE Function in Columns**

If you have a dataset of scorers and their goals in different leagues, you can find the average goal involvement. For example, to find the average goal involvement of player Ronaldo (in cell **C8**):

`=AVERAGE(C5:C6)`

- We have calculated the average goal involvement of
*Ronaldo.* - Use the
**Fill Handle**tool to copy the formula across to obtain the result for the rest of the players.

### Example 2 – Find the Average Percentages

If you need to find the** average of percentage values**, use the **AVERAGE** function.

For a range like **C5:C9** containing score percentages enter the following formula in cell** C11** and press **Enter** to get the result:

`=AVERAGE(C5:C9)`

### Example 3 – Calculating the Average Time

To calculate the **average time** from a dataset of marathon racers’ finishing times (formatted as **h:mm:ss**), enter the following formula in cell **C11**:

`=AVERAGE(C5:C9)`

The formula has produced the average time, and the format remains as the source time.

### Example 4 – Average of the Top N Values

Suppose you want to find the **average** of the** top 3 test scores**.

- Combine the
**LARGE**and**AVERAGE**functions in cell**H5**:

`=AVERAGE(LARGE(C5:G5,{1,2,3}))`

The **LARGE** function returns the **highest 3 values**, and then **AVERAGE** computes their **average**.

- Next, apply the
**Fill Handle**tool to copy the formula to the other cells.

**Note:** If you need to find the average of n number of least values you can use **SMALL **in place of **LARGE.**

**Example 5 – Find Average Ignoring 0**

Sometimes your dataset may contain **zeros**, but you want to calculate the average while excluding those zeros. In such cases, you can combine the **IF** function with the **AVERAGE** function. Here’s how:

- Suppose you have a dataset in cells
**C5:E5**, and you’ve inserted a zero in cell**D5**. - To find the
**average**of the**non-zero values**, use the following formula in cell**F5**:

`=AVERAGE(IF(C5:E5<>0,C5:E5))`

- Press
**Enter**, and you’ll get the average, ignoring the zeros.

**Formula Breakdown:**

**IF(C5:E5<>0, C5:E5)**: The**IF**function returns the range excluding zero values.**AVERAGE(IF(C5:E5<>0, C5:E5))**: The**AVERAGE**function then calculates the average for this modified range.

**How to Solve it When Excel AVERAGE Function Is Not Working**

Sometimes you might encounter issues with the **AVERAGE** function. For example, if you see a **#DIV/0!** error, it could be due to the following reason:

**Text Format**: If the numbers in a row are stored as text (instead of numeric values), the**AVERAGE**function won’t work correctly.

**Solution:**

- Select the
**numbers**. - Click on the
**error icon**. - Choose the
**Convert to Number**option from the menu.

The average formula will now work.

## Quick Notes

- You can provide numbers directly alongside cell references.

For instance, if you insert the number **9** with cell references **B5** through **B8** (containing 10, 9, 10, and 7), you’ll find the average.

- Besides using a range, you can set multiple cell references separated by
**commas**within the**AVERAGE**function.

- The
**AVERAGE**function can handle empty cells within the provided range; it ignores them and calculates the average for the remaining cells.

Here we have provided **B5** to **B9** and **B6** and **B8** are empty cells. The **AVERAGE **function will ** ignore **the empty cell and calculate the average for the rest of the cells.

**Download Practice Workbook**

You can download the practice workbook from here:

**<< Go Back to Excel Functions | Learn Excel**