Example 1 – Excel AVERAGEIF with ‘Greater Than’ Criteria
- To find the average for marks greater than 90, enter the following formula in cell H5.
=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")- To find the average marks in physics if the marks in math are greater than 90, enter the following formula instead in cell H5.
=AVERAGEIF(D5:D12,"<90",E5:E12)
Read More: Excel AVERAGEIF Function for Values Greater Than 0
Example 2 – Excel AVERAGEIF with ‘Less Than’ Criteria
- Enter the following formula in cell H6 to find the average marks in physics if the marks in math are less than 90.
=AVERAGEIF(D5:D12,"<90",E5:E12)
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
- To find the average marks for students with IDs greater than 2 and less than 7 i.e. in groups B and C, enter the following formula in cell H5.
=AVERAGEIFS(D5:D12,C5:C12,">2",C5:C12,"<7")
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 contains values other than numbers.
- 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
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
Get FREE Advanced Excel Exercises with Solutions!
