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

excel average with error in range

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.

excel average with error in range

b) As the second argument, select "6 - Ignore error values".

excel average with error in range

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.

excel average with error in range


You can find similar Excel Questions and Answer hereunder

1) How to trace errors in Excel. How to find where an error comes from in Excel

2) How can I get an estimate of Standard Deviation of a sample dataset?

3) what are the main range object and what can the range objects do in VBA

4) How can I find the sum of records that meet a given condition in my raw data table?

5) How can I quickly remove all blank cells in a data range?

6) What are the different type of errors in Excel VBA, subscript out of range, compile error, time out, run time overflow

7) How can I add a 3 Month Moving average Trend line to my line chart?

8) Here some more advanced range objects and what you can do with the range object in VBA

9) How to use the trace error function in VBA

10) How can I set FreezePanes in a certain range using VBA?


Here the previous and next chapter