## 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,`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:

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
`criteria`

is correctly written to avoid getting incorrect results or errors. - 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”. - 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"`

. - Remember that the
`AVERAGEIF`

function in Excel is not case-sensitive. Therefore, criteria like “Sales” and “sales” will be considered the same. - If no cells meet the requirements, the
`AVERAGEIF`

function will return the`#DIV/0!`

error.