Like all IF functions, AVERAGEIF will only calculate data within a spreadsheet if it meets specific criteria.
The AVERAGEIF function in Google Sheets will calculate the mean of the applicable cells from the columns or rows of the given range of cells.
When To Use AVERAGEIF in Google Sheets
You can use AVERAGEIF in several different circumstances. Here are a few examples, although this list is nowhere near exhaustive:
- Figure out the average commission of your top-performing sales employees
- Calculate average losses based on criteria such as accident or theft
- Work out average sales of a product by fiscal period
- Discover the average cost of rent for multiple location cities based on state or city
A Simple Example
Let’s consider a basic example of how AVERAGEIF could be useful.
Say a medical team is recording a trial for heart medication with three groups:
- A control group
- Patients receiving treatment A
- Patients receiving treatment B
During the trial, they regularly keep track of the heart rates of the patients. The scientist could easily use the AVERAGEIF function to calculate the average heart rate for each group simply by selecting the control group, treatment a, or treatment b as a criterion.
Not only that, they could use this function to calculate an average for data that meets any other criteria that may be on the spreadsheet such as gender, above or below a certain weight or age, using other medications, etc.
That’s just a tiny taste of the broad range of applications for the AVERAGEIF function.
Let’s take a look at how this function works.
The syntax of AVERAGEIF requires at least two arguments of the criteria_range and the criterion. You can also add the average_range for more specific calculations – this part of the syntax will often come up as most calculations that don’t require it can just use the AVERAGE function.
The syntax goes as follows:
=AVERAGEIF(criteria_range, criterion, [average_range])
What Does Each Part Of The Syntax Do?
- = The equal sign signifies to the spreadsheet that we are entering a function
- AVERAGEIF() The function – this contains the arguments and tells Google Sheets what calculation to do with that data
- criteria_range Indicates where to look for the criteria – in our previous example; you would select the cells that have which treatment the patients were receiving
- criterion This argument tells the spreadsheet which criterion in the critera_range must be met to be included in the calculation – in the example, it could be “treatment A”
- average_range This is where you put the range of data you want to work the average out for – in our example, it would be the heart rates of the patients
Take a look at the sample spreadsheet below to show precisely how the spreadsheet would work with our example:
You can see that the AVERAGEIF function calculated the average of only the patents that received Treatment A. We achieved this by substituting the following fields into the syntax:
If you’re still a little confused, that’s okay. Let’s look at a step-by-step guided example of completing the same calculation for Treatment B patients.
The Full Breakdown
- Select the cell that you want the calculation to show in. C16 in this example.
- Either type out =AVERAGEIF( or click on AVERAGEIF when Google Sheets suggests it
- Click and drag over the criteria range. In this case, it’s the treatment types, so cells B2:B11. You could also manually type this if you’d rather.
- Press the comma button “,”
- Type the criteria inside quotation marks – “Treatment B” for this sample
- Press the comma button again “,”
- Click and drag over or manually type the cell range for the data to be considered for the average. In the example, it is the heart rate data cells, so C2:C11
- Press the closing bracket key “)”
- Press Enter
That’s all you have to do to perform basic AVERAGEIF functions. But, they can get a little more complex.
Comparison Operators and the AVERAGEIF Function
Our above example was much neater than most real-life calculations you may need to make. In reality, you may need to work with only numerical figures and have to specify comparison criteria. That’s why the AVERAGEIF function is compatible with these six comparison operators:
- = (equals)
- <> (not equal to)
- > (greater than)
- < (less than)
- >= (greater than or equal to)
- <= (less than or equal to)
Placing each of these into your syntax will allow more control over your results or complete different calculations.
Let’s use our same spreadsheet and use the <= (less than or equal to) operator to provide an example.
Perhaps the researchers only want the average heart rate of patients 1-5. We can use the <= operator and the AVERAGEIF function to do this.
- This time we are going to select the patient numbers as our criteria_range
- Then enter the comparison operator and numerical value. For this example, that’s <=5 (less than or equal to 5)
- Then select the heart rate data again and press enter
For another example, let’s exclude patient 3 using the same process and the <> (not equal to) control. So, if we don’t want to include patient 3, we would have to use <>3
You can see that the syntax remains pretty much the same but with a different operator. The operators allow the inclusion or exclusion of data from the calculations.
AVERAGEIF Without an Average Range
If you don’t provide an average_range for the AVERAGEIF function to work with, it will instead find the average of the criteria range.
Take a look at the example below where we still used the <=5 operator, but no average range, so the function returned the average of the patient numbers instead.
This can also be a helpful calculation in some circumstances – just something to be aware of.
AVERAGEIFS vs. AVERAGEIF
Sometimes you may try to AVERIGEIF multiple ranges or AVERAGEIF Multiple Criteria. But this won’t work. To try that out, you’ll have to use the AVERAGEIFS function instead.
You can use AVERAGEIFS instead of AVERAGEIF if you need to consider:
- More than one data set
- Multiple criteria
- Multiple columns or rows
- Multiple ranges
Just think of the “S” as pluralizing the function. So if there is more than one of something in your potential formula, use AVERAGEIFS instead.
Unfortunately, AVERAGEIFS changes up the order of the syntax when compared to AVERAGEIF. So, despite them being very similar both in function and appearance, they are not compatible functions.
What to Learn Next
Learning how to master spreadsheet functions before they become necessary can help you get a head start on your business pursuits. The AVERAGEIFS function would be a good one to learn next as it fulfills a similar purpose but for more complicated spreadsheets. If your business already needs to use the AVERAGEIF function, AVERAGEIFS is not far behind.
Outside of that, there are plenty of other valuable spreadsheet functions and operations covered on our site; explore the categories that you think could be beneficial for your business. Stay productive!