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