Exceljet

Quick, clean, and to the point

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 set of cells to average. If you're not using average_range, the range will be used.

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

Now we can easily refer to "prices", "beds", "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 just need to provide a criteria of greater than zero. For range, we use "prices" and for criteria, we use ">0" in double quotes.

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

Now let's calculate an average for houses with a price less than $500k. We 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, we 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 different range 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", the criteria is ">=2000", and the average_range is "prices".

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

If 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.

Course 
Core Formula
Author 
Dave Bruns