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.

**Table of Contents**Expand

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

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

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

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

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

## 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**

- How to Calculate VLOOKUP AVERAGE in Excel
- How to Find Average with OFFSET Function in Excel
- How to Use VBA Average Function in Excel
- Calculate the Average of an Array with VBA
- How to Add Average Line to Excel Chart

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