AVERAGEIF Function in Excel

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:

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, range it 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_range the parameter is not provided, the cells  range that 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:

AB
1ProductSales
2Shoes150
3Bags200
4Shoes180

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.

AB
1DepartmentSalary
2HR5000
3Marketing7000
4HR5500

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.

AB
1MonthUnits
2January1000
3February1200
4January1100

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.

AB
1RegionProfit
2East500
3West700
4East600

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.

AB
1CategoryExpense
2Travel200
3Food150
4Travel250

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.”

ABC
1ProductSalesResult
2Shoes150=IF(AVERAGEIF(A2:A4,”Shoes”,B2:B4)=0,”No Sales”,AVERAGEIF(A2:A4,”Shoes”,B2:B4))
3Bags200
4Shoes180
5Result165

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.

ABC
1DepartmentScoreResult
2HR80=SUM(AVERAGEIF(A2:A5,”HR”,B2:B5), AVERAGEIF(A2:A5,”Finance”,B2:B5))
3Finance90
4HR70
5Finance85
6Result325

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.

ABCD
1RoleLevelSalaryResult
2EngineerLevel 15000=AVERAGEIF(A2:A5, VLOOKUP(C6,A2:C5,1,FALSE),C2:C5)
3ManagerLevel 28000
4EngineerLevel 15200
5ManagerLevel 28500
6SearchEngineer
7Result5100

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.

ABCD
1RegionProfitAverage ProfitCount
2East500=AVERAGEIF(A2:A4, “East”, B2:B4)=COUNTIF(A2:A4, “East”)
3West700
4East600
5Result5502

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.

ABC
1SubjectScoreResult
2Math90=MAX(AVERAGEIF(A2:A4,”Math”,B2:B4), AVERAGEIF(A2:A4,”Science”,B2:B4))
3Science80
4Math85
5Result90

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.

ABC
1ProductSalesResult
2Shoes150=MIN(AVERAGEIF(A2:A4,”Shoes”,B2:B4), AVERAGEIF(A2:A4,”Bags”,B2:B4))
3Bags200
4Shoes180
5Result165

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.

ABC
1CategoryExpenseResult
2Travel200=CONCATENATE(“Average Expense for Travel is “, AVERAGEIF(A2:A4,”Travel”,B2:B4))
3Food150
4Travel250
5Result“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.

ABC
1MonthTempResult
2January31.56=ROUND(AVERAGEIF(A2:A4,”January”,B2:B4),2)
3February28.45
4January30.47
5Result31.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

1. Make sure your criteria is correctly written to avoid getting incorrect results or errors.
2. You can use wildcard characters (like ? and *) in the criteria argument. For example, “A*” will match all cells containing a text string beginning with “A”.
3. If you want to use a logical operator (like > or <) As part of this criteria, you have to enclose it in quotes. For example, ">20".
4. Remember that the AVERAGEIF function in Excel is not case-sensitive. Therefore, criteria like “Sales” and “sales” will be considered the same.
5. If no cells meet the requirements, the AVERAGEIF function will return the #DIV/0! error.