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

Leave a Reply

Your email address will not be published.