The Google Sheets AVERAGEIF function returns the average of numbers that meet given criteria in a range. It is basically a combination of AVERAGE and IF functions and acts like an array formula to get the average of resulting array of numbers where a logical condition or criteria is TRUE.
The AVERAGEIF function in Google Sheets
For example, you have certain numbers, and you want to test a logical condition or criteria on those numbers, and then you need the average of those numbers where a logical condition is TRUE. So you can achieve this by using AVERAGEIF function in Google Sheets.
The syntax of the AVERAGEIF function in Google Sheets is;
AVERAGEIF(criteria_range, criterion, [average_range])
Here,
criteria_range – This is a range of values where you need to test logical condition or criteria.
Criterion – This is logical condition or criteria that you want to test on criteria_range. It can consist of a number or text or date or logical expression to test the condition.
average_range- This is a range of numeric values that you want to average. It is an optional argument, and if you do not supply this argument, the AVERAGEIF function takes criteria_range to calculate average.
Here you need to test the various criteria options to calculate the average of numbers that meet these criteria using the Google Sheets AVERAGEIF function.
AVERAGEIF for Number as criteria
You can use a number as criteria to calculate the average of those numbers that are equal to this criterion number. In this example, you want to calculate average marks of students in class 8th using Google Sheets AVERAGEIF function.
=AVERAGEIF(B2:B11,8,C2:C11)
AVERAGEIF for Text as criteria
If you want to test a text value as criteria on criteria_range then you need to supply the criteria in double quotation marks (“”) in the criterion argument of the Google Sheets AVERAGEIF function. For example, you have data of quantity sold of various foods’ categories, and you need to calculate the average quantity sold of vegetables using AVERAGEIF function in Google Sheets.
=AVERAGEIF(A2:A9,"vegetables",C2:C9)
AVERAGEIF for Date as criteria
The average can be calculated based on date as criteria using Google Sheets AVERAGEIF function. The date can be supplied directly or as a cell reference or as a date function like TODAY or DATE function in AVERAGEIF function in Google Sheets.
If you supply the date directly as criteria in the AVERAGEIF function, then you need to enter it in double quotation marks (“”), and if you enter it as cell reference or date function then it will be entered without double quotation marks, such as;
=AVERAGEIF(A2:A13,"08/15/2018",B2:B13)
OR
=AVERAGEIF(A2:A13,Date(2018,8,15),B2:B13)
AVERAGEIF for Expression as criteria
Based on criteria expression you can calculate average of numbers in Google Sheets AVERAGEIF function. Like if you want to calculate average of numbers that are Greater Than (>) or Greater Than Equal to (>=) or Less Than (<) or Less Than Equal to (<=) or Not Equal to (<>) to a specified number, then these expressions must be supplied in double quotation marks in criterion argument, such as;
Greater Than ➔ “>10”
Greater Than Equal to ➔ “>=10”
Less Than ➔ “<10”
Less Than Equal to ➔ “<=10”
Not Equal to ➔ “<>10”
For example, you need to take the average of students’ marks that are Greater Than Equal to 80 then formula would be;
=AVERAGEIF(B2:B11,">=80")
Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.
Leave a Comment