We can use the **AVERAGEIF** or **AGGREGATE function** to find the average of a list of values while ignoring any errors that might exist in the data. The steps below will walk through the process.

*Figure 1- Final result of the AVERAGEIF function*

**General Formula**

**=AVERAGEIF(values, “>=0”)**

**Formula**

**=AVERAGEIF(B5:B14, “>=0”)**

**Setting up the Data**

Our objective is to find the average of the list of values while ignoring all errors in the data.

- The list of values will be entered in the range
**B5:B14** - Cell
**F4 and F5**will contain the result after applying the**AVERAGEIF**function and**AGGREGATE**function respectively.

* Figure 2 – Setting up the Data*

**Applying the AVERAGEIF function**

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

**=AVERAGEIF(B5:B14, “>=0”)**

* Figure 3- How to use the AVERAGEIF function*

- We will press the
**enter**key

* Figure 4- Result of applying the AVERAGEIF function*

**Using the Alternate AGGREGATE function**

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

**=AGGREGATE(1,6,B5:B14)**

* Figure 5- How to use the AGGREGATE function*

- We will press the
**enter**key

* Figure 6- Result of using the AGGREGATE function*

**Explanation**

**AVERAGEIF function**

**=AVERAGEIF(values,">=0")**

The term **“values”** represents the range of the data: **B5:B14**. **AVERAGEIF** returns the average of the values that are greater than or equal to zero in the specified range, thus, filtering out any errors in the data.

**AGGREGATE function**

**=AGGREGATE(1,6,values)**

**“1”** specifies average while **“6”** represents the option to ignore errors. Like the AVERAGEIF function, the AGGREGATE function also returns with the same answer.

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