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

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.

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.

Download Practice Workbook

You can download the practice workbook from the download button below.

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.

Related Articles

• How to Find Average If Cell Contains Text in Excel
• Use Excel AVERAGEIF with Multiple Criteria
• How to Calculate Average If Cell Is Not Blank in Excel

<< Go Back to Excel AVERAGEIF Function | Excel Functions | Learn Excel