We can use the **AVERAGEIF function** to **calculate the average value** for a set of values in **excel if not 0**. Logical operators like **>, <, <>, =** and wildcards (***,?**) for partial matching are supported by the **criteria** of **AVERAGEIF**. The steps below will walk through the process.

*Figure 1- Final result of Average if not 0*

**Syntax**

**=AVERAGEIF(range,criteria,[average_range])**

- Range: This could be one or more cells, a named range, arrays, or references.
- Criteria: This could be a number, an expression, cell reference, or text.
- Average_range [optional]: These are the cells to get the average. If we omit it, range is used.

**Formula**

**=AVERAGEIF(C5:C16,">0")**

**Setting up the Data**

We will calculate the **average number of orders** for the year **2018** and **2019** while ignoring zeros in **figure 2.**

**The months will be entered into Column B****Column C and D**contain the**Orders**for**2018**and**2019**respectively- The
**average number of orders**while**ignoring zeros**for each year will be returned in**Column G**

* Figure 2 – Setting up the Data*

**Excel Average if not zero for 2018 and 2019**

- We will click on
**Cell G5** - We will insert the formula below into
**Cell G5**

**=AVERAGEIF(C5:C16,">0")** - We will press the
**enter**key

* Figure 3- Excel Average if not zero for 2018*

- We will click on
**Cell G5**again - We will
**double click**on the fill handle tool which is the small plus sign you see at the bottom right of**Cell G5.**Select and drag down to copy the formula to**Cell G6**.

* Figure 4- Result of Excel Average if not zero for 2018 and 2019*

**Explanation**

The **AVERAGEIF function** checks the **range** (C5:C16 and D5:D16) for values that are greater than zero. If there are such values, then the average of only those values are returned as the result.

**Note**

- Any empty cell and cell with logical values within the range are ignored by the
**AVERAGEIF Function** - The error value
**#DIV/0!**will be returned if there aren’t cells in the range that meet the criteria

**Instant Connection to an Expert through our Excelchat Service**

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment