Professionals can use Microsoft Excel to manage big data sets, keep track of records, and perform multi-digit computations. The AVERAGEIFS function is one such function that assists users in calculating the average while also adding other conditions to it. Understanding how to use this formula at work will help you hone your Excel skills if you’re looking to do so. In this post, we will define the AVERAGEIFS formula, look at its various components, learn how to use it, see some practical examples, and get some application advice.
What Is The AVERAGEIFS Function?
The AVERAGEIFS function is a pre-made Microsoft Excel formula that you can use to calculate the arithmetic mean of a range of values while considering one or more conditions. For example, if you want to calculate the average of only the numbers greater than zero and lesser than 100, you can use the formula and specify the two criteria. Initially, the AVERAGEIF function allowed users to calculate the average of values after checking for one criterion only, but this function now allows you to check for multiple criteria.
How to Use AVERAGEIFS Function
Here are some steps that you can follow to use this function in your datasets:
1. Organise your data in an Excel spreadsheet
The first step to use this function is to enter the data that you want to calculate the mean for in an Excel spreadsheet. Enter all the data by arranging them in columns and rows so that they become a range of data. You can then arrange the data alphabetically and save the data by saving the file. While there may be other columns specifying the criteria that you want to consider, create one column that comprises the average range. The average range has all the numbers for which you want to calculate the average.
2. Type in the formula
In a cell near the data table, enter the AVERAGEIFS formula and fill the syntax. Choose the column that has the numbers for which you want to calculate the average as the first syntax, average_range. In the next syntax, criteria_range1, select the column where you want to apply the first criterion.
You can then add the criteria that you want to apply by adding logical operators or wildcards. If you want to add more criteria, you can add subsequent ranges and subsequent criteria to the formula. Once you have added all the rules in the formula, you can close the brackets and press enter.
3. Select the range
You can also input criteria ranges for each section of the function by selecting them directly from the sheet instead of typing them yourself. One way to do this is by placing your mouse cursor over the bottom right of the top cell in the column until it shows a + symbol. You can then click and drag down the column so you can select every value. This can be useful if you work with smaller amounts of data or intend to calculate the mean one time.
Another way to do this is by double clicking the name of the column such as, C. This gives the benefit of applying the formula to the entire column for projects or values which you may enter or use later. This is also helpful if you have large amounts of data, which may make it time-consuming to select individually using the first method.
Components of the AVERAGEIFS Formula
Like all other Excel functions, the AVERAGEIFS formula also requires a particular format to be followed to give the correct result value. Here are the important parts of this function that are important to enter into a cell for it to produce the desired results:
Logical operators
In this function, a logical operator compares two or more values on the specified criteria. When a value matches the given criteria, you get a true value and if the value is different, typically you get a false value. The most commonly used logical operators are:
- = or equal to
- > or greater than
- < or lesser than
- >= or greater than or equal to
- <= or less than or equal to
- <> or not equal to
Wildcards
Wildcards are special characters in Excel that take the place of characters in formulas and apply to the AVERAGEIFS calculation. You can use them in your formula when there are incomplete matches in the data set. You can use a question mark to match any single character, for example, an? may match and, any or ant. An asterisk can match any sequence of characters from where you place it.
For example, if you write *fin*, it can match fit, fine, final or finale* from a data set. A tilde is used to match a particular character in a word. You can also use tilde if you want to match an asterisk or a question mark.
Syntax
The AVERAGEIFS formula is:
AVERAGEIFS (average_rang**e, criteria_range1, criteria1, [subsequent criteria ranges], [subsequent criteria])**
Here is the meaning of each of these syntaxes:
- Average_range: This is the range of cells that contain the data for which you want to calculate the average. You can select the entire table that contains the data or a specific column which contains the numbers.
- Riteria_range1: This is the range of cells where you want to apply the first criteria. While it may be the same as the average_range, it can be a subset within the table that you choose to calculate the mean.
- Criteria1: You can mention the first criteria that you want to apply to the first range in this syntax. For example, if you enter =AVERAGEIFS (A1:D20, B2:B20, >=10), the formula will return all values in the range B2:B20 that are greater than or equal to 10.
- Subsequent criteria ranges: If you want to add further specifications to the numbers before calculating the average, you can add more criteria for the same column, or another range of cells within the average range.
- Subsequent criteria: You can use this syntax to add more criteria to the ranges that follow the first one by following the same rules. For example, if you want to calculate only the values that have “TRUE” in column D, you can use =AVERAGEIFS (A1:D20, B2:B20, >=10, D2:D20, =”TRUE”).
AVERAGEIFS Application Example
You can use this function when you want to take multiple criteria into account while calculating the average of a range of data. Here is an example that can help you understand the application of this formula better:
A company’s sales head wants to analyse the performance of the sales department and understand the average sales in the first quarter of 2021. They want to understand the average performance under different conditions and identify the best-performing salespeople. After adding the names of the salespeople in column A, the sales manager can add the other details in the corresponding columns. They can then add the number of days a person worked in the quarter in column B, the number of sales a person made in column C and the number of clients a salesperson lost in column D.
Now the average range lies in column C and the criteria to be applied lie in columns B and D. The sales head wants to calculate the average of the salespeople who have brought more than 50 sales but have lost less than 10 clients. Considering these criteria, the formula that they can use is =AVERAGEIFS (C2:C31, B2:B31, “>=50”, D2:D31, “<10”). This formula excludes all the salespeople who have made fewer than 50 sales and have lost over 10 clients.
Tips for Applying AVERAGEIFS in Excel
Here are some tips that you can apply while using the AEVRAGEIFS function:
- Start the function with the equal to symbol. The equal sign helps Excel understand the data entered into a cell is a formula and requires calculation.
- Insert the arguments within brackets. Once you have written the name of the function, write the arguments for the function between parentheses. Parenthesis helps group related arguments together and also determines the order of evaluation.
- Use the ROUND function. If the AVERAGEIFS formula returns a decimal number, you can use the ROUND function to get the nearest integer. While you can add the ROUND function to a separate cell, you can also add it before the syntax by adding another parenthesis.
- Add quotes. When you mention the criteria that you want the AVERAGEIFS to apply to, mention the criteria between double-quotes.
- Enter the same sized ranges. Unlike the AVERAGEIF formula, it is important for all the criteria ranges to be of the same size as the average range.