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.
- You can also apply any of the alternative formulas below to get the same result.
- 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.
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.
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.
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.
Download Practice Workbook
You can download the practice workbook from the download button below.
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.