How to Calculate Average in Excel Excluding 0 (2 Methods)

Get FREE Advanced Excel Exercises with Solutions!

When we want to calculate an average in Microsoft Excel without a cell that contains zero, we can apply the AVERAGEIF, AVERAGE, and IF functions. Our today’s dataset is about different types of Products that have been ordered in different months. In this article, we’ll learn two quick and suitable ways how to calculate an average in Excel excluding 0 by using the AVERAGEIF, AVERAGE, and IF functions.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


2 Suitable Ways to Calculate Average in Excel Excluding 0

Let’s say, we have a dataset that contains information about different types of Products and their quantity that has been ordered in different Months are given in columns C, D, and B respectively. We’ll calculate an average of the Quantity of those ordered products excluding zero orders in several months in Excel. Here’s an overview of the dataset for today’s task.

average in excel excluding 0


1. Apply the AVERAGEIF Function to Calculate Average in Excel Excluding 0

We can easily calculate an average excluding 0 in Excel by applying the AVERAGEIF function. This is the easiest and most time-saving function to calculate an average excluding 0 in Excel. Please, follow the instructions below.

Steps:

  • First of all, merge cells E5 to E15. Then select the merged cells.

Apply the AVERAGEIF Function to Calculate Average in Excel Excluding 0

  • Further, type the AVERAGEIF function in the Formula Bar. The AVERAGEIF function in the Formula Bar is,
=AVERAGEIF(D5:D15, "<>0")
  • Where D5:D15 is the cell range of the function.
  • <>0 = criteria which means the cell’s value is greater than zero.

  • Hence, simply press Enter on your keyboard, and you will get an average excluding 0 as 81 which is the return of the AVERAGEIF function that has been given below screenshot.

  • After that, we count the cells that contain zero value, the average becomes 66.27. From the below screenshot, you will be able to understand the difference between the average of including and excluding zero.

Apply the AVERAGEIF Function to Calculate Average in Excel Excluding 0

Read More: How to Calculate Average in Excel (Including All Criteria)


Similar Readings


2. Insert AVERAGE and IF Functions to Calculate Average in Excel Excluding 0

In this method, we’ll calculate an average of the ordered products in different months excluding zero order in some months in Excel by applying the AVERAGE and IF functions. These functions can be applicable when cells are blank or contain text also. Let’s follow the steps below to learn!

Steps:

  • Select cell E5 first to calculate an average excluding 0.

Insert AVERAGE and IF Functions to Calculate Average in Excel Excluding 0

  • Hence, write down the AVERAGE and IF functions in the Formula Bar. The functions are,
=AVERAGE(IF(D5:D15<>0, D5:D15))
  • Where D5:D15<>0 = logical_test which means a cell that contains a value greater than zero.
  • D5:D15 = value_if_true which means the value of the cells.

Insert AVERAGE and IF Functions to Calculate Average in Excel Excluding 0

  • After that, press Enter on your keyboard, and you will get an average excluding 0 as 81 which is the return of the AVERAGE and IF functions that have been given below screenshot.

  • Further, we’ll calculate the average value of the cells including zero value, and the average including 0 becomes 27. From the below screenshot, you will be able to understand the average including and excluding zero.

Insert AVERAGE and IF Functions to Calculate Average in Excel Excluding 0

Read More: How to Exclude a Cell in Excel AVERAGE Formula (4 Methods)


Things to Remember

👉 The AVERAGEIF Function return #DIV/0! error when the value of all cells became non-numeric.

👉 If you’ve been using Excel 2003, you can apply a formula like this:

=SUM(range) / COUNTIF(range, “<>0”)

Conclusion

I hope all of the suitable methods mentioned above to calculate an average excluding zero will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo