Skip to content

Master the AVERAGEIF Function

In one of our previous articles, we discussed the three types of averages and how to calculate them. In this article, we will take a deep dive into the arithmetic mean and how to use the AVERAGEIF function.

The AVERAGEIF Function

The AVERAGEIF function allows you to calculate the arithmetic mean of cells in a spreadsheet that meet a specific condition.

For example, if you have a list of prices and would like to calculate the average price but only consider the prices greater than $10, then you would use the AVERAGEIF function.

The AVERAGEIF function also allows you to calculate the average for a range of cells based on a different range of cells. To understand this, let’s look at how the AVERAGEIF function works.

How the AVERAGEIF Function Works

The AVERAGEIF function requires two arguments to work, but it can have up to three.

=AVERAGEIF(criteria_range, criteria, average_range)
  • The first argument is the range evaluated against the criterion
  • The second argument is the criterion
  • The third argument is the range that is averaged

If only two arguments are used, then the criteria_range used for the first argument is used to calculate the average.

=AVERAGEIF(criteria_range, criteria)

The AVERAGEIF function will check to see which cells in the criteria_range meet the criterion. The function will then average the cells in the average_range that are in corresponding positions to the cells that meet the criterion in the first range.

Take a look at this example.

The AVERAGEIF is looking for the value “North” in the criteria_range.

The AVERAGEIF function then calculates the arithmetic mean of the cells in the average_range that are in the same row (corresponding position) as those cells containing the value “North.”

How to Use the AVERAGEIF Function

Here are two examples showing how to use the AVERAGEIF function.

The first example illustrates how to use the AVERAGEIF function with only two arguments.

=AVERAGEIF(A1:A8,">10")

In the example below, the AVERAGEIF function calculates the arithmetic mean for the cells which contain a value greater than 10.

The second example shows how to use the AVERAGEIF function with three arguments.

=AVERAGEIF(B1:B8,"Blue",A1:A8)

The AVERAGEIF function calculates the mean for only the cells that correspond with cells containing the text value “Blue.”

And there is your average explanation of how to use the AVERAGEIF function.

This Post Has One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top