In this article, I will show you different ways of how to calculate average in Excel, especially using the **AVERAGE function **for certain numbers, rows/columns as well as a range of cells. Apart from that, you will learn how to find the average of the top 5/ bottom 5, average based on single or multiple criteria. Moreover, you’ll get the practical use of **AVERAGEA**,** AVERAGEIF**, and **AVERAGEIFS** functions.

The **AVERAGE** formula in Excel is a powerful tool for calculating the mean value of a range of numbers. You may use the formula to save your time and effort and also for advanced calculations. Whatever the reason is, follow the article and enjoy.

**Download Practice Workbook**

You may download the following workbook to practice yourself.

## How to Calculate Average for Different Cases in Excel

**1. Average of Certain Numbers**

To calculate the average of certain numbers, give the numbers as the arguments of the **AVERAGE **function. You can give as much as you need. Look at the following formula in the **C5** cell and modify it in your worksheet.

`=AVERAGE(4,6,8,10,12)`

- After that, hit
**ENTER**and get the average.

**2. Average of a Row or Column**

- In order to calculate the average of a whole column, you will have to use the following formula.

`=AVERAGE(C:C)`

- Write down the formula in your desired cell and hit
**ENTER**to get the average of a certain column.

*Note:** Since we are calculating the average of Column C, we have given the argument C:C. You will have to modify this according to your requirements.*

*If you find out the average of a row, modify the formula like AVERAGE(3:3). Change the argument 3:3 according to your needs.*

**Read More:** How to Calculate Average of Multiple Ranges in Excel

**3. Average of a Range of Cells**

- To find out the average of a range of cells, follow the formula below.

**=AVERAGE(D7:E10)**

Here, **D7** is the starting point of the range, and **E10** is the end of the range.

- Following the structure, modify it according to your sheet and then hit
**ENTER**to get the output. Our dataset generated the output like the following image.

*Note:** Modify the formula according to your sheet to get the accurate result*.

**Read More:** How to Average a Column in Excel

**4. Average of Non-Adjacent Cells**

In order to calculate the average of non-adjacent cells, we will give the cell address as argument in the **AVERAGE **function. We will find the average of the numbers in the cells **C7**, **D9 **& **E5**.

- Write the following formula in a cell in your worksheet.

**=AVERAGE(C7,D9,E5)**

- After hitting
**ENTER**, you will get the output. Our dataset generated the following output.

**Read More:** How to Calculate Average of Multiple Columns in Excel

**5. Average of Multiple Ranges**

To find out the average of the numbers present in different ranges, give them as the arguments of **the AVERAGE function**, like in the following formula.

`=AVERAGE(C6:D7,D10:E11)`

- Modify the arguments (
*Ranges*) in the above formula and give it as input in a cell in your worksheet. - Hit
**ENTER**to get the output.

Our output looks like the following image.

**Read More:** How to Find Average with Blank Cells in Excel

**6. AVERAGE Function with Mixed Arguments**

In order to use the **AVERAGE **function with mixed arguments, we will use the following formula in our worksheet.

`=AVERAGE(C7:C10,D6,56)`

- Modify the above formula according to your sheet and hit
**ENTER**to get the output.

According to our dataset, we got an output like the following image.

**Read More:** How to Fix Divide by Zero Error for Average Calculation in Excel

## Practical Examples of Calculating Average in Excel

**1. Find Average Percentage **

In the following image, I will show you the dataset that we will use to calculate the average percentage.

- Now, write down the following formula into a cell in Excel and hit
**ENTER**to get the output.

`=AVERAGE(C5:C11)`

- Applying the formula to our dataset, we got the following output.

**Read More: **[Fixed!] AVERAGE Formula Not Working in Excel

**2. Calculate Average Time**

In the following image, you will see the dataset where we will apply the **AVERAGE** function to calculate the average time.

- At this stage, apply the following formula to a blank cell and hit
**ENTER**. We put the formula in**F8**and got the following output.

**Read More: **How to Calculate Average and Standard Deviation in Excel

**3. Find Average Without Zeros**

- To get the average free of zero, we will use
**the AVERAGEIF function**and give the range that includes some zeros. - Take a look at the formula below and modify it according to your dataset.

`=AVERAGEIF(E5:E10,"<>0")`

- Write down the formula and hit
**ENTER**to get the result.

According to our dataset, we got the following image.

**Note: **While doing the average of cells, keep in mind the difference between empty cells and those containing the value zero, especially if you have cleared the Show a zero in cells that have zero value in the Excel desktop application.

**4. Average of Best 5/Worst 5 in Excel**

**4.1 Average of Best 5**

- We will combine the
**AVERAGE**and**LARGE**functions to find the average of the best 5 values in a range. You may use the following formula to do that.

`=AVERAGE(LARGE(C5:C12, {1,2,3,4,5}))`

- Like the methods we applied before, use the formula in a cell and hit
**ENTER**to get the output.

You will get an output similar to the image below.

**Formula Breakdown**

**LARGE(C5:C12, {1,2,3,4,5}) :****The LARGE function**finds out the top 5 values in the array**C5:C12**.**AVERAGE(LARGE(C5:C12, {1,2,3,4,5})) :**Finds the average of the array of 5 top values returned by the**LARGE**function.

**Read More: **How to Add Average Line to Excel Chart

**4.2 Average of Worst 5**

- To find out the average of the smallest 5 values, we will combine the
**AVERAGE**and**SMALL**functions to find the average of worst 5 values in a range. You may use the following formula to do that.

`=AVERAGE(SMALL(C5:C12, {1,2,3,4,5}))`

- Use the formula in a cell and hit
**ENTER**to get the output. You will get an output similar to the following image

**Read More: **Calculate the Average of an Array with VBA

**5. Calculate Average Including Texts Using AVERAGEA Function**

If your dataset contains both numbers and texts, you may use the **AVERAGEA **function to calculate the average in that case.

- Use the following formula in your worksheet to get the average of the dataset, though it contains text.

`=AVERAGEA(B5:C12)`

- To get the result according to your dataset, modify the formula in your worksheet and get the average.

*Note:** The AVERAGEA function considers the text as 0 and then calculates the average. So the average may vary from the real one.*

**Read More: **How to Use VBA Average Function in Excel

**6. Get Average Based on Single Criteria Using AVERAGEIF Function**

To get the average based on criteria, we will use the formula below in cell **I9. **

`=AVERAGEIF(F5:F12,"B",C5:C12)`

Here, the function takes the first and second arguments as the criteria to calculate the average, while the third argument is the range where the function will apply the criteria.

After hitting** ENTER**, we got the result below.

**Read More: **How to Find Average with OFFSET Function in Excel

**7. Get Average Based on Multiple Criteria Using AVERAGEIFS Function**

- If you want to find out the average based on multiple criteria, use the formula below in your worksheet.

`=AVERAGEIFS(E5:E12,F5:F12,"A",E5:E12,">=70")`

Here, the **AVERAGEIFS** function has 3 arguments. The first one is the range where the function will calculate the average, and the second and third ones are the criteria based on which the function will calculate the average.

- Hit
**ENTER**to get the result.

*Note:** We have given 2 criteria in **the AVERAGEIFS function**. You may include as many as you need in your formula.*

**Read More: **How to VLOOKUP and AVERAGE Specific Values in Excel

## [Fixed!] AVERAGE Formula Not Working in Excel

If the dataset that you are using to calculate the average using the **AVERAGE **function contains non-numeric data, then you won’t get the exact average of the data. Rather, you will get a value that is not the actual average of the data.

In the following image, you can see that the data includes a non-numeric value (Absent) in **Column D**. Now, if you calculate the average using the **AVERAGE **function, you will get a value which is not the actual average of the data. Instead, we will use the **AVERAGEA** function to calculate the average.

Write down the following formula associated with the **AVERAGE** function in a blank cell to get a wrong average.

`=AVERAGE(D5:D12)`

The right formula to calculate the exact average looks like the following image.

`=AVERAGEA(D5:D12)`

Look at the following image to get an idea of the difference that these 2 formulas generate.

17-AVERAGE Formula Not Working in Excel

## AVERAGE vs AVERAGEA vs AVERAGEIF vs AVERAGEIFS

- The
**AVERAGE**function in Excel calculates the arithmetic mean of a range of cells. - The
**AVERAGEA**function is similar to**AVERAGE**, but it includes all values in the specified range, including text and logical values. It treats non-numeric values as zeros. - The
**AVERAGEIF**function calculates the average of a range of cells that meet a specified condition. - The
**AVERAGEIFS**function is an extension of**AVERAGEIF**and allows you to calculate the average based on multiple criteria.

## Things to Remember

- If the argument of
**AVERAGE**function refers to cells that contain**#VALUE!**errors, the formulas will result in a**#VALUE!**error. - If the range of cells you are trying to calculate has no numeric values, the
**AVERAGE**formula will return**#DIV/0!**error**.** - The
**AVERAGEIFS**function requires at least 2 criteria to work properly. - The
**AVERAGE**function can take exact values, cell references, and ranges in the arguments.

## Conclusion

This concludes our article on how to calculate average in Excel. Hopefully, this article was helpful for you and you can apply these methods in your worksheet to calculate the average of your dataset. Let us know if you have any further queries.

## Frequently Asked Questions

**1. What is the difference between AVERAGE and AVERAGEA formulas in Excel?**

The **AVERAGE** formula calculates the average of only numeric values, while the **AVERAGEA **formula includes all values in the range, treating non-numeric values as “0” for the purpose of averaging.

**2. Is it possible to include only certain cells from a range in the average calculation?**

Yes, you may use the **AVERAGEIF** or **AVERAGEIFS** function to do that.

**3. Does the AVERAGE formula include or exclude zero values from the calculation?**

The **AVERAGE **formula in Excel includes zero values in the calculation. When you use the **AVERAGE** formula, it considers all numeric values within the specified range.

## Average Formula in Excel: Knowledge Hub

**<< Go Back to How to Calculate in Excel | Learn Excel**