Calculate the average within a set of values and ignore any errors present, we can use AVERAGEIF function or its alternative AGGREGATE is used. The basic layout of this function is as follows:

*Figure 1: Average and ignore error function*

**The syntax of the function**

The syntax for the calculation of average and ignoring of any possible errors present the following syntax is used:

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

To explain the syntax, we refer to the example in figure1, the syntax is used in the cell E6 as:

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

where “values” is the range B6: B15

**Working of Function**

Referring to the above example present, the AVERAGEIF function will calculate the average of numbers within the criteria. According to this case, the criteria is the expression “>=0” which means that the function will filter out the values that present error. The AVERAGEIF returns the average of the remaining values after filtering which in this case is 90.75.

**Alternate Function**

An alternative to the AVERAGEIF, the AGGREGATE function also ignores the errors whose formula is as follows:

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

In this example, the numerical 1 is the specified average and 5 is the option that s needed for ignoring the error. The AGGREGATE will return the average of the remaining numbers in the range.

## Leave a Comment