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.
Download Practice Workbook
You can download the practice workbook from the download button below.
2 Uses of Excel AVERAGEIF Function for ‘Greater Than’ and ‘Less Than’ Criteria
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.
Read More: Excel AVERAGEIF Function for Values Greater Than 0
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.
Read More: How to Calculate Average If Number Matches Criteria in Excel
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.
Read More: How to Find Average If Values Lie Between Two Numbers in Excel
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.
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. You can also visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.