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