How to use the AVERAGEIF function

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

Here we have a list of 16 properties with prices and other information. Let's calculate some averages based on the conditions listed in column K.

The AVERAGEIF function calculates an average for cells that meet a single criteria. It takes three arguments: range, criteria, and average_range.

Note that average_range is optional and represents the actual range to average. If you're not using average_range, the range argument will be used instead.

First, I'm going to name some ranges to make the formulas easier to read and copy.

Now we can easily refer to "prices," "size," and "status."

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

Now let's calculate an average that excludes prices equal to zero. As you can see, the AVERAGE function automatically includes zero values because they are numeric.

To exclude zero values, we need to provide a criteria of greater than zero. For range, we use "prices" and for criteria, we use ">0" in double quotes.

If I check the average with the status bar, you can see that we're getting the correct number.

Now let's calculate an average for houses with a price less than $500,000. We need to use "prices" for range and

"<500,000" for criteria.

AVERAGEIF will throw an error if the criteria does not apply to any data in the range. For example, if we try to calculate an average for properties over $1 million dollars, we'll get a "divide by zero" error, because there are no properties with a price over $1,000,000.

When you want to apply the criteria to a range different from the range you're averaging, you'll need to supply the optional argument, average_range.

For example, to calculate an average for properties greater than or equal to 2000 square feet, the range is "size," and the criteria is ">=2000." The average_range is "prices."

To calculate an average for properties with a status of "Sold" the pattern is similar. The range is "status," criteria is "sold," and average_range is "prices."

When you need to calculate an average based on more than one criteria, you'll need to use the AVERAGEIFS function, which we'll cover in a separate video.