average with error in range in Excel

Q10. Is there a way I can average a range of numbers even if there is an error value in range?

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 excel average with error in range

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

excel excel 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 excel excel excel average with error in range

 

You can find similar Excel Questions and Answer hereunder

1) How can I clear all formats (formats alone not data) from a range of cells?

2) I have a cell with nested formulas that returns an error. In Excel, how can I step through the formulas to help with debugging?

3) How can I fill a range of cells with incremental times?

4) Sumifs with date range as criteria in Excel

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

6) How do I get the 2nd highest number in a range of numbers?

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

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

9) Is there a way to easily shade alternate rows in a data range for improving readability?

10) How do I find the median salary of employees with the same skillset in my Organization?

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

12) How can I set up a dynamic named range that expands automatically when new items are added to the list?

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

14) How can I remove hyperlinks from a range of cells?

15) How can I add a Prefix to a set of numbers in a range?