You want to calculate the average of numbers that meet certain criteria in a range of cells on Google Sheets? Look no further than the AVERAGEIF function! This function combines the AVERAGE and IF functions and acts as a matrix formula to average a set of resulting numbers where a logical condition is met.
The AVERAGEIF Function in Google Sheets
Let’s take an example: you have a list of numbers and you want to test a logical condition on those numbers, then get the average of the numbers that meet that condition. You can achieve this by using the AVERAGEIF function in Google Sheets.
The syntax for the AVERAGEIF function in Google Sheets is as follows:
=AVERAGEIF(criteria_range, criterion, [average_range])
In this formula:
criteria_range: This is the range of cells where you need to test the logical condition.criterion: This is the logical condition you want to test on thecriteria_range. This condition can be a number, text, date, or logical expression.average_range(optional): This is the range of numeric values you want to calculate the average of. It is an optional argument, and if you don’t specify it, the AVERAGEIF function will use thecriteria_rangeto calculate the average.
Now, let’s try out different criterion options to calculate the average of numbers that meet those criteria using the AVERAGEIF function in Google Sheets.
AVERAGEIF with a Number as Criterion
You can use a number as the criterion to calculate the average of numbers that are equal to that number. For example, you want to calculate the average of students’ grades in the 8th grade using the AVERAGEIF function in Google Sheets.
=AVERAGEIF(B2:B11, 8, C2:C11)
AVERAGEIF with Text as Criterion
If you want to test a textual value as the criterion on the criteria_range, you must provide the criterion within double quotes (“”) in the criterion argument of the AVERAGEIF function in Google Sheets. For example, you have data on the quantity sold for different food categories, and you need to calculate the average quantity sold of vegetables using the AVERAGEIF function in Google Sheets.
=AVERAGEIF(A2:A9, "vegetables", C2:C9)
AVERAGEIF with Date as Criterion
You can calculate the average based on the date as the criterion using the AVERAGEIF function in Google Sheets. The date can be provided directly, as a cell reference, or as a date function like TODAY or DATE in the AVERAGEIF function in Google Sheets.
If you provide the date directly as the criterion in the AVERAGEIF function, you must enclose it in double quotes (“”). If you enter it as a cell reference or date function, you should not use quotes. For example:
=AVERAGEIF(A2:A13, "08/15/2018", B2:B13)
OR
=AVERAGEIF(A2:A13, DATE(2018, 8, 15), B2:B13)
AVERAGEIF with Expression as Criterion
You can calculate the average of numbers based on an expression criterion in the AVERAGEIF function in Google Sheets. For example, if you want to calculate the average of numbers that are greater than (>), greater than or equal to (>=), less than (<), less than or equal to (<=), or not equal to (<>) a specified number, you need to provide those expressions within double quotes in the criterion argument. For example:
- Greater than ➔
">10" - Greater than or equal to ➔
">=10" - Less than ➔
"<10" - Less than or equal to ➔
"<=10" - Not equal to ➔
"<>10"
For instance, if you want to get the average of students’ grades that are greater than or equal to 80, the formula would be:
=AVERAGEIF(B2:B11, ">=80")
Need additional help with Excel formatting or other Excel-related questions? Connect with a live Excel expert here for personalized assistance. Your first session is always free.
Don’t forget, Crawlan.com is a great resource for more tips and information on Google Sheets and many other related topics.
