Part 1: Introduction to AVERAGEIF Function in Microsoft Excel
Definition
AVERAGEIF is a powerful function in Microsoft Excel that calculates the average (arithmetic mean) of a group of numbers based on a specific criterion or condition.
Purpose
The primary purpose of the AVERAGEIF function is to return an average of the numbers in a range that meets a specific criterion. This allows you to perform average calculations without including all the data in a given field.
Syntax & Arguments
The AVERAGEIF function has the following syntax:
AVERAGEIF(range, criteria, [average_range]) Let’s break down the arguments in this function:
range: This is the set of cells that you want to apply the criteria against.criteria: This is the condition or criteria of a number, expression, cell reference, or text that defines which cells will be averaged.average_range: This is an optional argument. If supplied, it’s the range of cells that will be averaged. If omitted,rangeit is used instead.
Return Value
The AVERAGEIF function will return the arithmetic mean of all the cells in the specified range that meet the defined criteria.
Remarks
- If the
average_rangethe parameter is not provided, the cellsrangethat meet the criteria are averaged. - The function will return an error if no cells meet the criteria.
Part 2: Examples
Here are five examples illustrating using the AVERAGEIF function in a business context.
Example 1
Purpose: To find the average sales of a particular product.
Let’s consider a small data set:
| A | B | |
|---|---|---|
| 1 | Product | Sales |
| 2 | Shoes | 150 |
| 3 | Bags | 200 |
| 4 | Shoes | 180 |
Formula: To find the average sales of Shoes, we will use AVERAGEIF function as =AVERAGEIF(A2:A4, "Shoes", B2:B4)
Result: The average sales of Shoes are calculated as 165.
Example 2
Purpose: To find the average salary of employees in a specific department.
| A | B | |
|---|---|---|
| 1 | Department | Salary |
| 2 | HR | 5000 |
| 3 | Marketing | 7000 |
| 4 | HR | 5500 |
Formula: To find the average salary of the HR department, we will use AVERAGEIF function as =AVERAGEIF(A2:A4, "HR", B2:B4)
Result: The average salary of the HR department is calculated as 5250.
Example 3
Purpose: To find the average number of units sold per month.
| A | B | |
|---|---|---|
| 1 | Month | Units |
| 2 | January | 1000 |
| 3 | February | 1200 |
| 4 | January | 1100 |
Formula: To find the intermediate units sold in January, we will use AVERAGEIF function as =AVERAGEIF(A2:A4, "January", B2:B4)
Result: The average units sold in January is calculated as 1050.
Example 4
Purpose: To find the average profit from a particular region.
| A | B | |
|---|---|---|
| 1 | Region | Profit |
| 2 | East | 500 |
| 3 | West | 700 |
| 4 | East | 600 |
Formula: To find the average yield from the East region, we will use AVERAGEIF function as =AVERAGEIF(A2:A4, "East", B2:B4)
Result: The average yield from the East region is calculated as 550.
Example 5
Purpose: To find the average expenses in a particular category.
| A | B | |
|---|---|---|
| 1 | Category | Expense |
| 2 | Travel | 200 |
| 3 | Food | 150 |
| 4 | Travel | 250 |
Formula: To find the average costs on Travel, we will use AVERAGEIF function as =AVERAGEIF(A2:A4, "Travel", B2:B4)
Result: The average costs on Travel are calculated as 225.
Example 6: Nested with IF Function
Purpose: To find the average sales of a particular product, and if no sales were made, return “No Sales.”
| A | B | C | |
|---|---|---|---|
| 1 | Product | Sales | Result |
| 2 | Shoes | 150 | =IF(AVERAGEIF(A2:A4,”Shoes”,B2:B4)=0,”No Sales”,AVERAGEIF(A2:A4,”Shoes”,B2:B4)) |
| 3 | Bags | 200 | |
| 4 | Shoes | 180 | |
| 5 | Result | 165 |
Result: The formula in cell C2 calculates the average sales of Shoes. Since there are sales, the result is 165.
Explanation: The IF function checks if the AVERAGEIF result is zero. If it is, “No Sales” is returned; otherwise, the average is displayed.
Example 7: Nested with SUM Function
Purpose: To find the sum of the average scores of different departments.
| A | B | C | |
|---|---|---|---|
| 1 | Department | Score | Result |
| 2 | HR | 80 | =SUM(AVERAGEIF(A2:A5,”HR”,B2:B5), AVERAGEIF(A2:A5,”Finance”,B2:B5)) |
| 3 | Finance | 90 | |
| 4 | HR | 70 | |
| 5 | Finance | 85 | |
| 6 | Result | 325 |
Result: The formula in cell C2 calculates the sum of the average scores of the HR and Finance departments, resulting in 325.
Explanation: Two AVERAGEIF functions are used to find the average score for both HR and Finance, and the SUM function adds these two averages together.
Example 8: Nested with VLOOKUP Function
Purpose: To find the average salary for a specific role using the VLOOKUP function.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Role | Level | Salary | Result |
| 2 | Engineer | Level 1 | 5000 | =AVERAGEIF(A2:A5, VLOOKUP(C6,A2:C5,1,FALSE),C2:C5) |
| 3 | Manager | Level 2 | 8000 | |
| 4 | Engineer | Level 1 | 5200 | |
| 5 | Manager | Level 2 | 8500 | |
| 6 | Search | Engineer | ||
| 7 | Result | 5100 |
Result: The formula in cell D2 calculates the average salary for the role “Engineer,” resulting in 5100.
Explanation: VLOOKUP is used to find the role to be average, and then AVERAGEIF calculates the average salary for that role.
Example 9: Nested with COUNTIF Function
Purpose: To find the average profit for a particular region and count how often that region appears in the data.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Profit | Average Profit | Count |
| 2 | East | 500 | =AVERAGEIF(A2:A4, “East”, B2:B4) | =COUNTIF(A2:A4, “East”) |
| 3 | West | 700 | ||
| 4 | East | 600 | ||
| 5 | Result | 550 | 2 |
Result: The formula in cell C2 calculates the average profit for the East region, and cell D2 counts how many times “East” appears, resulting in 550 and 2, respectively.
Explanation: The AVERAGEIF function calculates the average profit for the East region, while COUNTIF counts the number of occurrences.
Example 10: Nested with MAX Function
Purpose: To find the maximum average score among different subjects.
| A | B | C | |
|---|---|---|---|
| 1 | Subject | Score | Result |
| 2 | Math | 90 | =MAX(AVERAGEIF(A2:A4,”Math”,B2:B4), AVERAGEIF(A2:A4,”Science”,B2:B4)) |
| 3 | Science | 80 | |
| 4 | Math | 85 | |
| 5 | Result | 90 |
Result: The formula in cell C2 calculates the maximum average score among Math and Science, resulting in 90.
Explanation: Two AVERAGEIF functions calculate the average scores for Math and Science, and the MAX function returns the higher of the two averages.
Example 11: Nested with MIN Function
Purpose: To find the minimum average sales among different products.
| A | B | C | |
|---|---|---|---|
| 1 | Product | Sales | Result |
| 2 | Shoes | 150 | =MIN(AVERAGEIF(A2:A4,”Shoes”,B2:B4), AVERAGEIF(A2:A4,”Bags”,B2:B4)) |
| 3 | Bags | 200 | |
| 4 | Shoes | 180 | |
| 5 | Result | 165 |
Result: The formula in cell C2 calculates the minimum average sales among Shoes and Bags, resulting in 165.
Explanation: Two AVERAGEIF functions calculate the average sales for Shoes and Bags, and the MIN function returns the lower of the two averages.
Example 12: Nested with CONCATENATE Function
Purpose: To find the average expense and concatenate it with the text description.
| A | B | C | |
|---|---|---|---|
| 1 | Category | Expense | Result |
| 2 | Travel | 200 | =CONCATENATE(“Average Expense for Travel is “, AVERAGEIF(A2:A4,”Travel”,B2:B4)) |
| 3 | Food | 150 | |
| 4 | Travel | 250 | |
| 5 | Result | “Average Expense for Travel is 225” |
Result: The formula in cell C2 calculates the average expenses for Travel and concatenates it with a descriptive text, resulting in "Average Expense for Travel is 225".
Explanation: AVERAGEIF calculates the average expenses for Travel, and CONCATENATE joins this result with a text string to create a more descriptive result.
Example 13: Nested with ROUND Function
Purpose: To find the average temperature for a particular month and round it to two decimal places.
| A | B | C | |
|---|---|---|---|
| 1 | Month | Temp | Result |
| 2 | January | 31.56 | =ROUND(AVERAGEIF(A2:A4,”January”,B2:B4),2) |
| 3 | February | 28.45 | |
| 4 | January | 30.47 | |
| 5 | Result | 31.02 |
Result: The formula in cell C2 calculates the average temperature for January and rounds it to two decimal places, resulting in 31.02.
Explanation: The AVERAGEIF function calculates the average temperature for January, and the ROUND function rounds this average to two decimal places.
Part 3: Tips and Tricks
- Make sure your
criteriais correctly written to avoid getting incorrect results or errors. - You can use wildcard characters (like
?and*) in thecriteriaargument. For example, “A*” will match all cells containing a text string beginning with “A”. - If you want to use a logical operator (like
>or<) As part of thiscriteria, you have to enclose it in quotes. For example,">20". - Remember that the
AVERAGEIFfunction in Excel is not case-sensitive. Therefore, criteria like “Sales” and “sales” will be considered the same. - If no cells meet the requirements, the
AVERAGEIFfunction will return the#DIV/0!error.