Nazmul Hossain Shovon
In this tutorial, I am going to show you 5 quick tricks to calculate the average only for cells with values in excel. You can quickly use these methods even in large datasets to find the average for the data cells especially if there are some blank cells in them. Throughout this tutorial, you will also learn some important excel tools and functions which will be very useful in any excel related task.
Download Practice Workbook
You can download the practice workbook from here.
5 Quick Tricks to Calculate Average Only for Cells with Values in Excel
We have taken a concise dataset to explain the steps clearly. The dataset has approximately 6 rows and 4 columns. Initially, we formatted all the cells containing dollar values in Accounting format. For all the datasets, we have 2 unique columns as Item and Sales units. Although we may vary the number of columns later on if that is needed.
1. Applying AVERAGE Function
The AVERAGE function in excel can calculate the arithmetic mean of the values that we provide as inputs. We can use this function to calculate the average of only the cells with some values. Follow the steps below.
Steps:
- First, go to cell D10 and insert the following formula:
=AVERAGE(B5:E9)
- Now, press Enter and this will calculate the average of the total sales.
Read More: How to Calculate Average, Minimum And Maximum in Excel (4 Easy Ways)
2. Utilizing AVERAGEA Function
The AVERAGEA function works similarly to the previous function except that it also considers texts, TRUE and FALSE values. Let us see how to use this function to calculate the average of cells only with values in excel.
Steps:
- To begin with, double-click on cell D10 and enter the below formula:
=AVERAGEA(C5:C9,E5:E9)
- Next, press the Enter key and you should get the average of only the cells with values.
Read More: How to Average a Column in Excel (7 Easy Methods)
3. Average with Criteria by AVERAGEIF Function
The AVERAGEIF function in excel requires a criteria range to work with, unlike the previous ones. We can use this criteria field to calculate the average of cells with values only. Let us see how to use this function.
Steps:
- To begin this method, double-click on cell D10 and insert the formula below:
=AVERAGEIF(B5:E9,"<>0")
- Next, press the Enter key and consequently, this will find the Sales Average inside cell D10.
Read More: How to Calculate Average of Multiple Ranges in Excel (3 Methods)
Similar Readings
- How to Calculate Average Percentage Increase in Excel
- Calculate Average Percentage of Marks in Excel (Top 4 Methods)
- [Fixed!] AVERAGE Formula Not Working in Excel (6 Solutions)
- Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)
- How to Calculate Running Average in Excel (3 Ideal Examples)
4. Using AVERAGEIFS Function
The AVERAGEIFS function in excel can calculate the average of cells only with values using multiple criteria. Follow the steps below to achieve this.
Steps:
- To start this method, navigate to cell D10 and type in the following formula:
=AVERAGEIFS(B5:E9,B5:E9,"<>0")
- After that, press the Enter key or click on any blank cell.
- Immediately, this will give you the average for the cells that have some values.
Read More: How to Calculate Average of Multiple Columns in Excel (6 Methods)
5. Average Database with DAVERAGE Function
The DAVERAGE function in excel works with databases and can find the average of cells only with values if we set the proper criteria. Let us see how we can use this function.
Steps:
- As previously, insert the below formula inside cell G6:
=DAVERAGE(B4:D10,"Price",F4:F5)
- Finally, press the Enter key and you should get the average price for the color Red.
Read More: How to Calculate Average and Standard Deviation in Excel
How to Calculate Average If Cells Contain Text in Excel
We can use the AVERAGEIF function in excel to look for text inside cells and then count the average of them accordingly. Follow the steps below to apply this function.
Steps:
- First, go to cell E5 and insert the following formula:
=AVERAGEIF(B5:B10,"*John*",C5:C10)
- Next, press Enter and this will find the average points for John as we set as the text contain criteria.
Read More: How to Calculate Average of Text in Excel (2 Ways)
Conclusion
I hope that you were able to apply the methods that I showed in this tutorial on how to calculate the average only for cells with values in excel. As you can see, there are quite a few ways to achieve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.
Related Articles
- Average Every Nth Row in Excel (3 Ways)
- How to Calculate Average Growth Rate in Excel (3 Easy Methods)
- Calculate Average of Averages in Excel (with Easy Steps)
- How to Calculate Average Deviation in Excel Formula
- Calculate Average Rating in Excel (7 Methods)
- How to Calculate 7 Day Moving Average in Excel (4 Ways)
- Calculate Moving Average for Dynamic Range in Excel (3 Examples)
