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.
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.
- 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
Get FREE Advanced Excel Exercises with Solutions!
