## Abstract

## Transcript

In this video we'll look at how to use the AVERAGEIFS function to calculate an average from numbers that meet multiple criteria.

Here we have a list of 16 properties with prices and other information. Let's calculate some averages based on the criteria shown in column K. Note that this data already contains a number of named ranges. We have "prices," "beds," "size," "listed," and "status."

The AVERAGEIFS function can calculate an average for cells that meet multiple criteria. The first argument is *average_range;* this is the range that contains the values to average. The criteria is supplied in pairs: first the *range*, and then the *criteria*.

To start off, let's calculate a straight average with the AVERAGE function for reference.

Like AVERAGE and AVERAGEIF, the AVERAGEIFS function will automatically exclude blank values, but zero values are included. Let's calculate an average that excludes zero values.

Although AVERAGEIFS allows you to provide multiple criteria, you can easily use it to get an average based on one condition only. In this case, *Average_range* is "prices," *criteria_range1* is "prices," and *criteria1* is "greater than zero."

Next, we need to calculate an average for prices greater than zero and less than $500,000. In this case, a*verage_range* is again "prices," *criteria_range1* is "prices," and *criteria1* is "greater than zero." To limit the average to prices less than $500,000, we'll need to supply another range and criteria pair: *criteria_range2* is "prices," and *criteria2* is "<500,000".

Using the status bar, I can quickly verify that we do, indeed, have the right average.

The AVERAGEIFS function will throw an error if no values in the range meet the criteria. We'll get a "divide by zero" error if we try to get an average for properties below $100,000 since there aren't any in the list.

To get an average for properties with two or more bedrooms and greater than 2500 SF, a*verage_range* is "prices," *criteria_range1* is "beds," *criteria1* is ">=2"; then *criteria_range2* is "size," and *criteria2* is ">2500".

To get an average for properties listed in 2012, we use "prices" for *average_range.* Then we need to supply "listed" twice as a *criteria range*. Once with a *criteria* of "> Dec 31, 2011," and again with a criteria of "< Jan 1, 2013."