How to Ignore Error Values When Calculating the Average in Excel

This post will guide you how to ignore error values when getting average for a range of cells in Excel. How do I average a range of cells ignoring error values, such as: #div/0!.

Assuming that you have a list of range of cells that you want to calculate the average of those cells which including error values , such as: #div/0 or #NAME?, etc. When you use the Average function to calculate those cells, you will get an error result. So How to handle this in Excel.

average cells ignore error1

If you want to ignoring any errors that might exist in the given range, you can use AVERAGEIF function or AVERAGE function with IF function to achieve the result. Like this:

=AVERAGEIF(A1:C4,”>=0”)

Or

=AVERAGE(IF(ISERROR(A1:C4),””,A1:C4))

OR

=AVERAGE(IF(ISNUMBER(A1:C4),A1:C4))

 

average cells ignore error2

average cells ignore error3

Note: The second and third formula need to press Ctrl + Shift +  Enter keys, as those formulas are Array Formula. A1:C4 is the data range that you wish to average. And you need to change it as you need.

Let’s see how the first formula works: the AVERAGEIF function can be used to calculate an average of numeric values with one or more criteria for a given range. And In this example, the criteria is the expression “>=0”, it will filter out all error values, and just to calculate the positive numeric values in the given range.

 

Related Functions


  • 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 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 IFERROR function
    The Excel IFERROR function returns an alternate value you specify if a formula results in an error, or returns the result of the formula.The syntax of the IFERROR function is as below:= IFERROR (value, value_if_error)….
  • Excel ISNUMBER function
    The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
Related Posts

If Cell Contains Certain Text OR Equals Certain Text

IF cell equals certain text IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to test values to see if they equal certain text like ...

Basic Usage of INDEX & MATCH – Case Sensitive Lookup

In Excel, INDEX function and MATCH function are often used together for retrieving data from a particular position. MATCH function is one of Excel lookup & reference functions that can perform approximate match or exact match by setting different match ...

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 Count Cells that Contain only numbers in Excel
count cells that contain number2

This post will guide you how to count the number of cells that only contain numbers within a range of cells using a formula in Excel 2013/2016. Count Number of Cells that Contain Numbers Assuming that you have a data ...

How to Count Cells that Contain X or Y in Excel
count cell that contain x or y7

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

How to Extract Initials From a Name in Excel
extract initials from a name2

This post will guide you how to get initials from a given name using a formula in Excel. How do I extract initials from names in Excel 2013/2016. Extract Initials from a Name Using a Formula Extract Initials from a ...

How to Extract Number from Text String in Excel
extract number from text string3

This post will guide you how to extract number from a given test string in Excel. How do I extract all numbers from string using a formula in Excel. How to get all number from a given test string using ...

How to Filter Cells Starts with Number or Letter in Excel

This post will guide you how to filter values starts with number or letter in one single cell in Excel. How do I filter in a list those that begin with number or letter using a formula in Excel. Filter ...

How to Limit Data Entry in a Cell in Excel
limit data entry in cell8

This post will guide you how to limit cell entries to specific length and number of digits in Excel. How do I limit data entry to allow only text or only numbers entered into in a cell using Data Validation ...

How to Ignore Blank Cells in a Formula in Excel
ignore blank cells in a formula3

This post will guide you how to ignore blank cells in a formula in Excel. How do I ignore blank cells when performing calculations in a formula in Excel. Ignore Blank Cells in a Formula Video:Ignore Blank Cells in a ...

Sidebar