Average ignoring errors
This tutorial shows how to average values in a range ignoring cells with errors using Excel formulas, with the AVERAGE, IF, ISERROR and AGGREGATE functions
|
=AGGREGATE(1,6,B5:B11)
|
GENERIC FORMULA
=AGGREGATE(1,6,range)
ARGUMENTS EXPLANATION This formula uses the AGGREGATE function with the function_num criteria set to 1, which represents an AVERAGE function, and the options criteria set to 6, which tells the function to ignore error values from the selected range.
Please note that this formula will ignore blank cells and will not account for them when averaging the values in the selected range.
In this example the formula averages the values captured in cells B5, B7, B9 and B11 ignoring the error cells (B6 and B10) and the blank cell (B8). |
|
GENERIC FORMULA
{=AVERAGE(IF(ISERROR(range),"",range))}
ARGUMENTS EXPLANATION This is an array formula that uses the AVERAGE, IF and ISERROR functions to return the average of the value in the selected range.
Please note that this formula will take blank cells into consideration when averaging the values in the selected range. The formula will treat the blank cells as containing a value of zero (0).
In this example the formula averages the values captured in cells B5, B7, B8, B9 and B11 ignoring the error cells (B6 and B10). |
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Average numbers and ignore zeros | How to average numbers in a range and ignore zeros | |
| Average values if cells are not blank | How to average value if cells are not blank | |
| Average values if cells are equal to | How to average values if corresponding cells are equal to a specified value |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| AVERAGE Function | The Excel AVERAGE function returns the average value from a specified range | |
| IF Function | The Excel IF function performs a test on specified conditions entered into the formula and returns a specified value if the result is TRUE or another specified value if the result is FALSE |