2 Easy Ways to Compute Average Excluding 0 in Excel

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.

Overview image to compute average excluding 0 in Excel


📂  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.

Sample dataset containing the Grade Point Average calculation

APPROACH

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 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.

Using the AVERAGEIF function to compute the average excluding 0

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

APPROACH

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 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.

Using the AVERAGE and IF functions

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 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.

Using the AVERAGE function to compute the average including 0 in Excel

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.

Options from the File tab

  • 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.

Unchecking a certain criterion from the Excel Options dialogue box

  • Now you will see that the cells containing 0 are blank.

Blank cells after unchecking the Show a zero in cells that have zero value

  • Belatedly, you have to select the 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.

The result after using the AVERAGE function to compute the average ignoring the blank cells in Excel

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

(Visited 18 times, 1 visits today)
Jonayed

Jonayed

Hello! My name is Jonayed. I've completed my graduation from BUET in Naval Architecture & Marine Engineering. As Excel is very interesting to me, I like to do various types of research regarding Excel. My inquisitive nature drives me to find solutions to different types of problems with Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo