# Excel AVERAGEIF with ‘Greater Than’ and ‘Less Than’ Criteria

Get FREE Advanced Excel Exercises with Solutions!

This article illustrates how to apply the Excel AVERAGEIF function for values less than and greater than specific numbers. The AVERAGEIF function in Excel allows us to calculate the average of values that meet the specific condition. Follow the article to use this function with the “greater than” (>) and “less than” (<) conditions.

## How to Use Excel AVERAGEIF Function for ‘Greater Than’ and ‘Less Than’ Criteria: 2 Practical Examples

We will use the following dataset to apply the Excel AVERAGEIF function for the “Greater Than” and “Less Than” conditions. ### 1. Excel AVERAGEIF with ‘Greater Than’ Criteria

• Assume you need to find the average for marks greater than 90. Then you can simply enter the following formula in cell H5 to see the following result.
` =AVERAGEIF(D5:D12,">90")` • You can also apply any of the alternative formulas below to get the same result.
`=AVERAGE(IF(D5:D12>90,D5:D12,""))`
`=SUMIF(D5:D12,">90")/COUNTIF(D5:D12,">90")`
• Now how can you find the average marks in physics if the marks in math are greater than 90? Well, apply the following formula instead in cell H5 to be able to do that.
`=AVERAGEIF(D5:D12,"<90",E5:E12)` ### 2. Excel AVERAGEIF with ‘Less Than’ Criteria

• You can apply the following formula in cell H6 if you need the average marks in physics if the marks in math are less than 90.
`=AVERAGEIF(D5:D12,"<90",E5:E12)` ## How to Calculate Average in Excel Using AVERAGIFS Function If a Range Is Between Two Values

• Now assume you need to find the average marks for students with IDs greater than 2 and less than 7 i.e. in groups B and C. Then apply the following formula in cell H5.
`=AVERAGEIFS(D5:D12,C5:C12,">2",C5:C12,"<7")` ## Things to Remember

• Don’t forget to put double quotes around the criteria if it doesn’t contain numbers only.
• All of the criteria ranges in the AVERAGEIFS function must be the same in size and shape as the average range.
• The formulas may return #DIV/0! if no cells meet the criteria.

## Conclusion

Now you know how to use the Excel AVERAGEIF function with the “Greater Than” and “Less Than” conditions. Do you have any further queries or suggestions? Please let us know in the comment section below. Stay with us and keep learning.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  