Average with error in range in Excel
For example, in the data set below Average formula for Q1-2016, returns #N/A since one of the records has an error value for that column
To do it in Excel, here is the answer:
Use Aggregate function as described below when dealing with a range that has one or more error values.
a) Enter Aggregate. As the first argument, select the type of measure. In this example case, select 1-AVERAGE.
b) As the second argument, select "6 - Ignore error values".
c) As the third argument, select the range for which the average is to be calculated. The result ignoring the record with error value is now displayed.