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 combination of AVERAGEIF, AVERAGE, and IF functions. 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 to calculate an average in Excel excluding 0 by using the AVERAGEIF, AVERAGE, and IF functions.


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 the average 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. Applying 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.

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 formula based on the AVERAGEIF function is:
=AVERAGEIF(D5:D15, "<>0")
  • Where D5:D15 is the cell 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 Average Values Greater Than Zero in Excel


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

In this method, we’ll calculate an average of the ordered products in different months excluding zero orders in some months in Excel by applying the AVERAGE and IF functions. These functions can be applicable when cells are blank or contain text.

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 formula will be:
=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


Things to Remember

👉 The AVERAGEIF function returns #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”)

Download Practice Workbook

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


Conclusion

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


Related Articles


<< Go Back to Conditional Average | Calculate Average | How to Calculate in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo