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.


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.

dataset for AVERAGEIF


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")

Excel AGERAGEIF function with "Greater Than"

  • 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)

averageif with greater than

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.
=AVERAGEIF(D5:D12,"<90",E5:E12)

Excel AVERAGEIF with "Less Than"

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.
=AVERAGEIFS(D5:D12,C5:C12,">2",C5:C12,"<7")

average if greater than and less than

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.

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. You can also visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.


Related Articles

Md. Shamim Reza

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

Leave a reply

ExcelDemy
Logo