Explanation

In this example, the goal is to average a list of values that may contain errors. The values to average are in the named range data (B5:B15). Normally, you can use the AVERAGE function to calculate an average. However, if the data contains errors, AVERAGE will return an error. You can see this in cell E5, which contains the average function:

=AVERAGE(data) // returns #N/A

This happens because B9 and B13 contain the #N/A errors, and this is a common problem in Excel: errors in the data tend to percolate up to summary calculations.

AVERAGEIF

One way to work around this problem is to use the AVERAGEIF function , which can apply a condition to filter values in a range before they are averaged. For example, to specifically ignore #N/A errors, you can configure AVERAGEIF like this:

=AVERAGEIF(data,"<>#N/A") // ignore #N/A errors

In the worksheet shown, this is the formula in cell E8. This works fine as long as the data contains only #N/A errors, but it will fail if there are other errors in the data. Another option with AVERAGEIF is to select only numbers that are greater than or equal to zero:

=AVERAGEIF(data,">=0") // zero or greater

This is the formula in E7. This simple formula works fine, as long as the numbers to average are not negative.

AGGREGATE

The simplest and most robust way to ignore errors when calculating an average is to use the AGGREGATE function . In cell E6, AGGREGATE is configured to average and ignore errors by setting function_num to 1, and options to 6:

=AGGREGATE(1,6,data) // average and ignore errors

This formula in E6 will ignore all errors that might appear in data, not just the #N/A error, and it will work fine with negative values. The AGGREGATE function is a “Swiss Army knife” function that can run other functions like SUM, COUNT, AVERAGE, MAX, etc. with special behaviors. For example, AGGREGATE can optionally ignore errors, hidden rows, and even other calculations. AGGREGATE can perform 19 different functions .

AVERAGE and IFERROR

It is possible to write an array formula that uses the AVERAGE function with the IFERROR function to filter out errors before averaging:

=AVERAGE(IFERROR(data,""))

Note: this is an array formula and must be entered with Control + Shift + Enter, except in Excel 365 , where arrays are native .

IFERROR returns an alternative result when there is an error, and the original result when there is not. In this formula, the IFERROR function is used to catch errors in the data and convert them to empty strings (""). In the example shown, the named range data (B5:B15) contains eleven cells, which can be represented as an array like this:

{98;95;88;95;#N/A;75;90;100;#N/A;84;91} // 11 values in B5:B15

IFERROR converts the #N/A errors to empty strings ("") like this:

=IFERROR(data,"")
=IFERROR({98;95;88;95;#N/A;75;90;100;#N/A;84;91},"")
{98;95;88;95;"";75;90;100;"";84;91}

The resulting array is returned directly to the AVERAGE function :

=AVERAGE({98;95;88;95;"";75;90;100;"";84;91}) // returns 90.67

AVERAGE automatically ignores text values and returns the same result as above: 90.67.

AVERAGE and FILTER

Finally, in Excel 365 , you can use the FILTER function together with the ISNUMBER function to filter out errors before they are averaged with a formula like this:

=AVERAGE(FILTER(data,ISNUMBER(data)))

Note: this is an array formula, but it only works in Excel 365 , where arrays are native .

Here, the ISNUMBER function tests each value in data and returns an array of TRUE and FALSE values like this:

{TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE}

Because there are 11 values in data , ISNUMBER returns 11 TRUE / FALSE results. TRUE corresponds to numeric values, and FALSE corresponds to non-numeric values. This array is returned directly to the FILTER function as the include argument:

FILTER(data,{TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE})

FILTER then returns a “filtered array” that contains the 9 numeric values to AVERAGE:

=AVERAGE({98;95;88;95;75;90;100;84;91}) // returns 90.67

and AVERAGE returns 90.67.

Note: Be careful when ignoring errors. Suppressing errors can hide underlying problems.

Explanation

In this example, the goal is to create a formula that calculates an average by group, using the group names in column C. The solution shown requires three general steps:

  1. Create an Excel Table called data
  2. List unique groups with the UNIQUE function
  3. Calculate averages with the AVERAGEIFS function

Create the Excel Table

One of the key features of an Excel Table is its ability to dynamically resize when rows are added or removed. In this case, all we need to do is create a new table named data with the data shown in B5:D16. You can use the keyboard shortcut Control + T .

Video: How to create an Excel table

The table will now automatically expand or contract as needed when new data is added or removed.

List unique groups

The next step is to list the unique group values in column C starting in cell F5. For this we use the UNIQUE function . The formula in F5 is:

=UNIQUE(data[Group]) // unique groups

The result from UNIQUE is an array with 3 values like this:

{"A";"B";"C"}

This array lands as a spill range in cell F5 and lists all of the unique groups in the Group column of the table. The UNIQUE function will continue to output a list of unique groups, even if new groups are added to the data.

Video: Intro to the UNIQUE function

Video: What is an array ?

Calculate average

We now have what we need to calculate the average for each group. Our task is to calculate an average based on one criteria: the group name. A good way to do this is to use the AVERAGEIFS function, which is designed to calculate averages based on one or more criteria. Because we have unique groups in a spill range , we can point to this list directly. The formula in G5 is:

=AVERAGEIFS(data[Score],data[Group],F5#)

The first argument in AVERAGEIFS is average_range . This is the range that contains numbers to average. In this example, this is the Score column in the table:

=AVERAGEIFS(data[Score],

The next two arguments specify the criteria. The criteria range is data[Group], and the criteria is the spill range:

=AVERAGEIFS(data[Score],data[Group],F5#)

Because the spill range F5# contains 3 values, the AVERAGEIFS function returns 3 averages in an array like this:

{77.75;73.25;87.25}

Each number in the array is the average calculated for a group. These values spill into the range G5:G7.

When data changes

The key advantage to this approach is that it responds instantly to changes in the data. If new rows are added that refer to existing groups, the spill range returned by UNIQUE remains unchanged, and AVERAGEIFS calculates an updated set of averages. If new rows are added that include new groups, or if existing group values are changed, these changes are captured by the UNIQUE function, which expands the spill range in F5 if needed. If rows are deleted from the table, the spill range contracts as needed. In all cases, the spill range represents the current list of unique groups, and the AVERAGEIFS function uses this list to calculate averages.

Legacy Excel

In older versions of Excel without the UNIQUE function, the approach needs to be modified slightly. Instead of using the UNIQUE function to automatically extract unique groups, the groups can be entered manually. Then the formula in G5 becomes:

=AVERAGEIFS(data[Score],data[Group],F5)

Note the criteria is no longer a spill range. This formula can then be copied down to return an average for each group.

Note: there are ways to extract unique values in older versions of Excel, but they are more complicated.

Pivot Table option

A pivot table would also be a good way to solve this problem , and would provide additional features. However, one drawback is that pivot tables need to be refreshed to show the latest data. Formulas, on the other hand, update instantly when data changes.