Average and ignore errors

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.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment