Excel: How to Calculate Average If Between Two Values


You can use the following formula to calculate the average of values in a range in Excel only for the values that fall between two specific values:

=AVERAGEIFS(B:B,B:B,">=90",B:B,"<=95")

This particular formula will only calculate the average for the values that fall between 90 and 95 in column B.

The following examples show how to use this formula in practice.

Example 1: Calculate Average If Between Two Values in Excel (Using One Range)

Suppose we have the following dataset that shows the exam scores received by 15 students: 

We can use the following formula to calculate the average exam score only for students who received a score between 90 and 95:

=AVERAGEIFS(A:A,A:A,">=90",A:A,"<=95")

The following screenshot shows how to use this formula in practice:

The average exam score only for students who received a score between 90 and 95 is 92.4

We can manually verify that this is correct:

Average Exam Score = (90 + 92 + 92 + 93 + 95) / 5 = 92.4.

Example 2: Calculate Average If Between Two Values in Excel (Using Multiple Ranges)

Suppose we have the following dataset that shows the height (in inches) and points scored by 15 basketball players:

We can use the following formula to calculate the average exam points scored only for players who have a height between 70 and 75 inches:

=AVERAGEIFS(B:B,A:A,">=70",A:A,"<=75")

The following screenshot shows how to use this formula in practice:

The average points scored for players who are between 70 and 75 inches turns out to be 17.833.

We can manually verify that this is correct:

Average Points Scored = (14 + 14 + 16 + 19 +20 + 24 / 6 = 17.833.

Note: You can find the complete documentation for the AVERAGEIFS function here.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Calculate a Cumulative Average in Excel
How to Find Weighted Moving Averages in Excel

One Reply to “Excel: How to Calculate Average If Between Two Values”

  1. Hello! This works great if you want a single calculation from a data set. But if you want to drag the formula down, it does not understand that you want to change the criteria when you drag the formula down. So if I want to calculate the average for values btween “0” and “1” and between “1”and “2” and so on… than you have to do it manually for every number range. Is there a way to be able to drag this formula down so that the criteria range updates to “+1” each time? the range of the values for the average and for the criteria stay the same btw.

Leave a Reply

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