How to Average and Ignore Errors in Excel

This post will guide you how to average a list of values and ignoring any errors in the given range in Excel 2013/2016 or Excel office 365. You can use the AVERAGEIF function or AGGREGATE function to calculate the average of a range of cells which might include some errors in Excel.

Average Cells Ignoring Errors


Assuming you want to calculate the average of the cells that excluding any errors in range B1:B6, and you can use the following formula based on the AVERAGEIF function, like this:

=AVERAGEIF(B1:B6,”>10”)

average and ignore errors1

Let’s See How This Formula Works:

The AVERAGEIF function can be used to calculate an average of some numeric values with one or more criteria. In the above example, the criteria is the expression of “>10”, and it means that you want to average all cell value in range B1:B6 which might include error values.

If you want to calculate the average of the cell values that ignoring any errors that might exist in the given range. And you can also use another array formula based on the AVERAGE function, the IF function and the ISERROR function. Like this:

=AVERAGE(IF(ISERROR(B1:B6),””,B1:B6))

Then you need to put this formula in a cell, and press Ctrl+Shift+Enter keys to make it as array formula. and you should see that the average result would be calculated.

average and ignore errors2

Related Functions


  • Excel AVERAGE function
    The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
  • Excel AVERAGEIF function
    The Excel AVERAGEAIF function returns the average of all numbers in a range of cells that meet a given criteria.The syntax of the AVERAGEIF function is as below:= AVERAGEIF (range, criteria, [average_range])….
  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel ISERROR function
    The Excel ISERROR function used to check for any error type that excel generates and it returns TRUE for any error type, and the ISERR function also can be checked for error values except #N/A error, it returns TRUE while the error is #N/A. The syntax of the ISERROR function is as below:= ISERROR (value)….

 

 

Related Posts

How to Statistic Pass 4 Out of 5 Rounds in a Competition by Excel Functions
Statistic Pass 4 Out of 5 Rounds 1

Suppose you are counting the numbers of participants who are the winners in a competition. And the passing line is provided as pass 4 out of 5 rounds of games. You need to know if participant pass the game or ...

Average the Last N Numeric Values in Excel
Average the Last N Numeric Values 7

AVERAGE function is one of the most popular functions in Excel. Apply AVERAGE together with some other functions, we can calculate average simply for some complex situations. In this article, we will introduce you to calculate average of the last ...

How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column
How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column14

Sometimes we may meet the case that subtotal values for different groups and only record a subtotal value for one group in a column, for other cells in this group, keep them blank. Today we will introduce you how can ...

Running Count of Occurrence List
running count of occurrentce in list2

This post will guide you how to get running count of an occurrence in a list with formula in Excel 2013/2016 or Excel office 365. How to create a running count of certain values that appear in the given range ...

How to Count Unique Numeric Values with Criteria in a Range in Excel
count unique numeric values criteria9

We have talked that how to count unique numeric values in a data set in Excel in the previous post. And this post will guide you how to count unique numeric values with criteria in the range in Excel 2013/2016 ...

How to Count Cells that do not Contain Errors in Excel
count cell do not contain error5

This post will guide you how to count the number of cells that do not contain errors within a range of cells using a formula in Excel 2013/2016 or Excel office 365. How do I count the number of cells ...

How to Count Number of Cells that Contain Errors in Excel
count number cells that contain errors6

This post will guide you how to count the number of cells that contain errors within a range of cells using a formula in Excel 2013/2016.You can easily to count cells equal to a specific value or text string through ...

How to Sum by Group in Excel

Sometimes we may have the requirement that subtotal data by group in a table. To just subtotal data in a column, we can use SUM function directly; for data in a column, amount data in another column, we can use ...

How to Compare Two Columns and Remove the Duplicate Values by Formula in Excel
How to Compare Two Columns and Remove the Duplicate Values by Formula 9

To compare two columns and remove the duplicate values, we have already posted a tutorial about how to solve this problem by ‘Conditional Formatting’ feature ‘Remove Duplicate’ rule. Actually, there are some other ways to solve it as well. If ...

How to Compare Two Columns to Find Missing Value (Unique Value) in Excel
Compare Two Columns 11

Suppose we have two lists, list A contains all students, list B only contains students who passed exam. So, list A is longer than list B, and students in list B are all included in list A. If we want ...

Sidebar