Table of Contents
Excel is a powerful tool that can be used to easily calculate the average of a range of numbers that fall between two specified values. By using the AVERAGEIF function, you can specify the range of numbers and the criteria that they must meet (in this case, falling between the two specified values). Excel will then calculate the average of only those numbers that meet the specified criteria, providing an accurate and efficient way to calculate averages within a specific range. This feature is particularly useful for analyzing data sets with a specific range of values, such as test scores or budget numbers.
Excel: 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:
=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 .
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I use Excel to calculate the average of a range of numbers if they fall between two specified values?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-excel-to-calculate-the-average-of-a-range-of-numbers-if-they-fall-between-two-specified-values/
stats writer. "How can I use Excel to calculate the average of a range of numbers if they fall between two specified values?." PSYCHOLOGICAL SCALES, 1 Jul. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-excel-to-calculate-the-average-of-a-range-of-numbers-if-they-fall-between-two-specified-values/.
stats writer. "How can I use Excel to calculate the average of a range of numbers if they fall between two specified values?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-excel-to-calculate-the-average-of-a-range-of-numbers-if-they-fall-between-two-specified-values/.
stats writer (2024) 'How can I use Excel to calculate the average of a range of numbers if they fall between two specified values?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-excel-to-calculate-the-average-of-a-range-of-numbers-if-they-fall-between-two-specified-values/.
[1] stats writer, "How can I use Excel to calculate the average of a range of numbers if they fall between two specified values?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, July, 2024.
stats writer. How can I use Excel to calculate the average of a range of numbers if they fall between two specified values?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.