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