Jonayed
In Excel, there is an excellent technique that you can use to compute the average excluding 0 (zero). There will be a significant difference between the computing average excluding 0 and the computing average including 0 in Excel. We have used two Excel functions (AVERAGE and IF) to compute the average excluding 0. In this article, we will show you how to compute the average in Excel excluding 0 with 2 easy approaches. We have also included some relevant concerns at the end of this article. Let’s say we have a dataset with grades and points for different subjects of a certain semester. We will calculate the Grade Point Average (GPA) excluding the F grade. So, let’s get started.
📂 Download Excel File
Download free Excel workbook from here:
Learn to Compute Average Excluding 0 in Excel with These 2 Approaches
While writing this article, “How to Compute Average Excluding 0 in Excel”, we’ve considered a dataset that contains approximately 5 columns and 15 rows. In this dataset, we added Subject, Grade, Point, and GPA to make you better understand. But if you want, you can change the entities and put your own values.
1. Using AVERAGEIF Function
If you want to compute the average excluding 0 in Excel then using the AVERAGEIF function of Excel will be very helpful for you. It is a simple and handy approach to achieve. You can do it easily without facing any difficulty. This function will assist you to compute the average excluding 0 in Excel. Follow the given steps to achieve the method.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select the E cell.
- Secondly, write the following formula on that cell.
=AVERAGEIF(D6:D15, “<>0”)
🔨 Formula Breakdown
Here,
👉 The range is D6 to D15 where the average will be computed and “<>0” refers that the values which are greater than 0 and will be counted.
- Thirdly, press the Enter key.
- Finally, you will see the desired result you wanted to have.
Undoubtedly, this approach is one of the easiest approaches if you want to compute average excluding 0 in Excel.
📕 Read More: 3 Approaches to Calculate 5 Star Rating Average in Excel
2. Adopting AVERAGE and IF Functions Combined
In the previous step, we used the AVERAGEIF function to compute the average excluding 0. Now we will be going to use the AVERAGE and IF functions separately. The main difference is the AVERAGEIF function computed the average and then excluded 0 but if you intend to use the AVERAGE and IF functions separately, there the IF function will exclude 0 from the range of values at first and then the AVERAGE function will compute the average. Maintain the following steps.
⬇️⬇️ STEPS ⬇️⬇️
- Primarily, you need to select the E cell.
- Secondly, write the given formula on that cell.
=AVERAGE(IF(D6:D15<>0, D6:D15))
🔨 Formula Breakdown
Here:
👉 A cell that has a value larger than zero is defined as one in which D6:D15<>0 is a logical test.
👉 D6:D15 is the range for computing the average.
- Thirdly, tap on the Enter key.
- Finally, you will see the required result you wanted to see.
Truly, this method is a very easy way to compute the average excluding 0 in Excel.
📕 Read More: 6 Quick Ways to Calculate Average Percentage of Marks in Excel
How to Compute Average Including 0 in Excel
We have discussed the ways to calculate the average excluding 0 in Excel. In this portion, we are going to discuss computing average including 0 using the same dataset. This procedure is not very difficult to do. We will apply the AVERAGE function to make the process simple. You just need to follow the given instructions to achieve the approach.
⬇️⬇️ STEPS ⬇️⬇️
- First, you have to select the E cell.
- Secondly, write the following formula on that certain cell.
=AVERAGE(D6:D15)
- Afterward, you need to press the Enter key.
- Eventually, you will see the required result you wanted to see.
Indeed, this method is very simple and handy to work on.
📕 Read More: 7 Easy Ways to Calculate Average Rating in Excel
How to Calculate Average Ignoring Blanks in Excel
Sometimes we want to calculate the average by ignoring the blank cells in Excel. To do that, we will first make the cells blank that are containing no value or 0. After that, we will apply the Excel function to compute the average. The resultant will be the value without blank cells or ignoring the blank cells. Maintain the following steps so that you can do it easily.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, you need to go to the File tab.
- Secondly, choose the Options.
- Then you will get a dialogue box name Excel Options.
- Thirdly, select the Advanced option.
- Afterward, uncheck Show a zero in cells that have zero value.
- Click on the OK button.
- Now you will see that the cells containing 0 are blank.
- Belatedly, you have to select the E cell.
- After that, insert the following formula on that certain cell.
=AVERAGE(D6:D15)
- Then press the Enter key.
- Consequently, you will see the required result you wanted to see.
Undoubtedly, you can easily compute the average by ignoring the blank cells in Excel by following the above approach.
📕 Read More: 6 Ways to Solve When Average Formula is Not Working in Excel
📄 Important Notes
You should be aware of the following things while performing the processes mentioned above:
🖊️ You should be aware of using Excel functions while applying formulas.
🖊️ You need to practice if you have less knowledge about using the functions of Excel.
📝 Takeaways from This Article
If we summarize the whole article, we have got some points.
📌 Initially, we applied the AVERAGEIF function to compute the average excluding 0 in Excel.
📌 Secondly, we used the AVERAGE and IF functions separately.
📌 Afterward, we tried to show you how to compute the average including 0 in Excel.
📌 Finally, we computed the average ignoring the blank cells in Excel.
Conclusion
We wish that you would be able to compute the average excluding 0 in Excel using this article. As several methods have been shown here, you can easily follow which one is best suited for you. If you have any confusion, feel free to ask me in the comment. In the end, to learn more about Excel tactics, we recommend you to visit our website www.ExcelDen.com.
Related Articles
- 7 Ways to Calculate Average Attendance with Formula in Excel
- 3 Approaches to Calculate Average of Text in Excel
- How to Calculate Average Percentage Change in Excel
- 5 Ways to Calculate 7-Day Moving Average in Excel
- Calculate Displaced Moving Average with Formula in Excel
- 2 Examples to Calculate Centered Moving Average in Excel
- 7 Quick Ways to Calculate Average Percentage Increase in Excel
