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

**Table of Contents**hide

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

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

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

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

**Similar Readings**

**[Fixed!] AVERAGE Formula Not Working in Excel (6 Solutions)****How to Get Average Time in Excel (3 Examples)****Generate Moving Average in Excel Chart (4 Methods)****How to Calculate VLOOKUP AVERAGE in Excel (6 Quick Ways)****Calculate 5 Star Rating Average in Excel (3 Easy Ways)**

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

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

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

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

**Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)****How to Calculate Average and Standard Deviation in Excel****Calculate Class Average in Excel (6 Easiest Methods)****Moving Average Formula in Excel (8 Uses with Examples)****How to Calculate Average Percentage Change in Excel (3 Simple Ways)**