AVERAGEIFS and IFERROR

Now we’ll determine the average sales by region, where the Salesperson has fifty or more orders.

We can do this with the AVERAGEIFS function.

First, we select the range of cells we want to average, in this case cells D2 through D7 in the Sales column.

I am pressing F4 to make this an absolute cell reference.

Next, we’ll select the first range of cells we want to evaluate, the range of cells in the Region column.

Then, we select the criteria by which the range is evaluated, in this case East, cell F2.

Then, we select the range of cells for the number of Orders and the criteria it needs to meet.

In the North region, for Salespeople having 50 or more orders, their average sales are $63,370.

Let’s walk through this. It is similar to the SUMIFS function.

First, the function evaluates how many cells in the Region column contain a specific region. In this example, North.

There are two, Mo and Dave are the salespeople for the North region.

Of these two, SUMIFS then evaluates how many have orders greater than or equal to 50. For North, this is both Mo and Dave.

Lastly, the function averages the cells from the range of cells in the Sales column where all the corresponding criteria are met.

The average of Mo and Dave’s Sales is $63,370.

You’ll notice that the average for the South region is a divide by zero error.

I’ll show you why and how to handle this in a minute.

AVERAGEIFS returns the average of all cells that meet multiple criteria.

This is the syntax of the AVERAGEIFS function.

average_range is required. It is one or more cells to average. Blank and text values are ignored.

criteria_range1 is required. It is the first range that is evaluated.

criteria1 is required. It is a criteria by which criteria_range1 is evaluated.

criteria_range2, criteria2, and so on are optional.

Each of the cells in the average_range is used in the average calculation, only if all the corresponding criteria specified are TRUE for that cell.

All criteria_range must be the same size and shape as average_range.

You can use the ? and * wildcard characters in criteria.

As we saw before, the average for the South region returns a divide by zero error.

Wei is the only Salesperson in the South region, and she has fewer than 50 orders.

If there are no cells that meet all the criteria, AVERAGEIFS returns the divide by zero (#DIV/0!) error.

We can enhance the formula with the IFERROR function to handle this and other error conditions.

I am putting the AVERAGEIFS function inside an IFERROR function.

IFERROR returns the value specified, in this case NA, if AVERAGEIFS evaluates to an error; otherwise, it returns the result of the formula.

And we see that the South region no longer returns an error.

This is the syntax of the IFERROR function.

Value is required. It is the argument that is checked for an error.

Value_if_error is also required. It is what IFERROR returns if the value argument returns an error.

Now you have a pretty good idea about how to use IF functions in Excel.

Of course, there is always more to learn.

So, check out the course summary at the end, and best of all, explore Excel 2013 on your own.