Introduction
The AVERAGEIF function in Excel calculates the average of numbers that satisfy a specific condition. For example, you can calculate the average sales of the North region, average salary of IT employees, or average marks of students who passed. In this tutorial, you will understand how conditional averaging works through simple explanations and practical examples.
1. What is the AVERAGEIF Function in Excel?
AVERAGEIF is a conditional calculation function.
It calculates the average of numbers only when the selected records satisfy a particular condition.
First, let’s understand what an average means.
Suppose three employees generate the following sales:
| Employee | Sales |
|---|---|
| Amit | 10000 |
| Neha | 20000 |
| Rahul | 30000 |
The average sales are:
[
\frac{10000+20000+30000}{3}=20000
]
Now imagine you want the average sales of only employees from the North region.
This is where AVERAGEIF becomes useful.
Instead of calculating the average of all employees, Excel:
- Checks the Region column.
- Finds records matching North.
- Selects the corresponding sales values.
- Calculates their average.
Important Concept: AVERAGEIF does not calculate the average of every number in the worksheet. It calculates the average of numbers connected to records that satisfy your condition.
2. AVERAGEIF Function Syntax
=AVERAGEIF(range, criteria, [average_range])
Let’s understand each argument.
| Argument | Meaning |
|---|---|
range | Cells Excel checks against the condition |
criteria | The condition that must be satisfied |
average_range | Cells containing the numbers to average |
Understanding the Arguments With a Simple Example
Consider this employee data:
| Employee | Department | Salary |
|---|---|---|
| Amit | IT | 30000 |
| Neha | HR | 25000 |
| Rahul | IT | 40000 |
| Priya | Sales | 35000 |
Suppose you want the average salary of IT employees.
=AVERAGEIF(B2:B5,"IT",C2:C5)
Understand the formula:
B2:B5– Excel checks the Department column."IT"– Excel looks for employees belonging to IT.C2:C5– Excel calculates the average using the Salary column.
The matching salaries are ₹30,000 and ₹40,000.
[
\text{Average}=\frac{30000+40000}{2}=35000
]
<text color=”success” weight=”semibold”>Expected Result: ₹35,000</text>
3. Understanding How Excel Checks Conditions
A condition is simply a rule that Excel follows.
For example:
"IT"means the department must be IT.">30000"means the number must be greater than 30,000."<40000"means the number must be less than 40,000."<>HR"means the department must not be HR.
Comparison Operators
| Operator | Meaning | Example |
|---|---|---|
= | Equal to | "=IT" |
> | Greater than | ">30000" |
< | Less than | "<40000" |
>= | Greater than or equal to | ">=30000" |
<= | Less than or equal to | "<=40000" |
<> | Not equal to | "<>HR" |
Excel checks each cell against your condition and selects the matching records for averaging.
For example, if the condition is ">30000", a salary of ₹35,000 matches, but ₹25,000 does not.
Practical Examples of AVERAGEIF Function in Excel
Question 1: Calculate Region-Wise Average Sales
Problem Statement
A company wants to calculate the average sales generated by employees in the North region.
Sales Data
| Order ID | Region | Sales |
|---|---|---|
| O101 | North | 15000 |
| O102 | South | 12000 |
| O103 | North | 18000 |
| O104 | East | 10000 |
| O105 | South | 14000 |
| O106 | North | 21000 |
| O107 | North | 16000 |
Excel Formula
=AVERAGEIF(B2:B8,"North",C2:C8)
Expected Result
<text color=”success” weight=”semibold”>Average Sales = ₹17,500</text>
Explanation
Excel checks the Region column and selects sales values belonging to North.
Matching sales:
- ₹15,000
- ₹18,000
- ₹21,000
- ₹16,000
Average:
[
\frac{15000+18000+21000+16000}{4}=17500
]
Concept Covered: Calculating an average based on a text condition.
Question 2: Calculate Department-Wise Average Salary
Problem Statement
An HR manager wants to calculate the average salary of employees working in the IT department.
Employee Data
| Employee ID | Employee | Department | Salary |
|---|---|---|---|
| E101 | Amit | IT | 35000 |
| E102 | Neha | HR | 30000 |
| E103 | Rahul | IT | 45000 |
| E104 | Priya | Sales | 40000 |
| E105 | Karan | IT | 50000 |
| E106 | Simran | HR | 32000 |
Excel Formula
=AVERAGEIF(C2:C7,"IT",D2:D7)
Expected Result
<text color=”success” weight=”semibold”>Average IT Salary = ₹43,333.33</text>
Explanation
Excel checks the Department column and selects salaries of IT employees.
Matching salaries:
- ₹35,000
- ₹45,000
- ₹50,000
Average:
[
\frac{35000+45000+50000}{3}=43333.33
]
Concept Covered: Department-wise average salary calculation.
Question 3: Calculate Average Marks of Students Who Scored More Than 60
Problem Statement
A training institute wants to calculate the average marks of students who scored more than 60.
Student Data
| Student | Marks |
|---|---|
| Amit | 85 |
| Neha | 45 |
| Rahul | 65 |
| Priya | 55 |
| Karan | 90 |
| Simran | 70 |
Excel Formula
=AVERAGEIF(B2:B7,">60")
Expected Result
<text color=”success” weight=”semibold”>Average Marks = 77.5</text>
Explanation
Here, the marks column is used both to check the condition and calculate the average.
Excel selects marks greater than 60:
- 85
- 65
- 90
- 70
Average:
[
\frac{85+65+90+70}{4}=77.5
]
Concept Covered: Using a numerical condition without specifying average_range.
Question 4: Calculate Average Revenue for a Specific Product
Problem Statement
A retail company wants to calculate the average revenue generated by Laptop sales.
Product Sales Data
| Order ID | Product | Revenue |
|---|---|---|
| O101 | Laptop | 50000 |
| O102 | Mouse | 12000 |
| O103 | Laptop | 45000 |
| O104 | Keyboard | 18000 |
| O105 | Laptop | 60000 |
| O106 | Mouse | 10000 |
| O107 | Laptop | 35000 |
Excel Formula
=AVERAGEIF(B2:B8,"Laptop",C2:C8)
Expected Result
<text color=”success” weight=”semibold”>Average Laptop Revenue = ₹47,500</text>
Explanation
Excel checks the Product column and selects revenue values for Laptop.
Matching revenue:
- ₹50,000
- ₹45,000
- ₹60,000
- ₹35,000
Average:
[
\frac{50000+45000+60000+35000}{4}=47500
]
Concept Covered: Product-wise average revenue.
Question 5: Calculate Average Sales Above a Specific Amount
Problem Statement
A sales manager wants to calculate the average of transactions greater than ₹20,000.
Sales Data
| Order ID | Sales |
|---|---|
| O101 | 15000 |
| O102 | 25000 |
| O103 | 35000 |
| O104 | 18000 |
| O105 | 40000 |
| O106 | 12000 |
| O107 | 30000 |
Excel Formula
=AVERAGEIF(B2:B8,">20000")
Expected Result
<text color=”success” weight=”semibold”>Average Sales = ₹32,500</text>
Explanation
Excel selects only sales values greater than ₹20,000.
Matching sales:
- ₹25,000
- ₹35,000
- ₹40,000
- ₹30,000
Average:
[
\frac{25000+35000+40000+30000}{4}=32500
]
Concept Covered: Average calculation using a greater-than condition.
Question 6: Calculate Average Sales Using a Cell Reference
Problem Statement
A company wants to create a flexible report where the user can enter a region name in a separate cell and calculate its average sales.
Sales Data
| Order ID | Region | Sales |
|---|---|---|
| O101 | North | 15000 |
| O102 | South | 12000 |
| O103 | North | 18000 |
| O104 | East | 10000 |
| O105 | South | 14000 |
| O106 | North | 21000 |
| O107 | North | 16000 |
Enter North in E2.
Excel Formula
=AVERAGEIF(B2:B8,E2,C2:C8)
Expected Result
| Selected Region | Average Sales |
|---|---|
| North | 17500 |
Explanation
Instead of typing North directly into the formula, Excel reads the condition from E2.
Change E2 to South or East to calculate the average sales of another region.
Concept Covered: Using a cell reference as a condition.
Question 7: Calculate Average Salary for Employees Not in HR
Problem Statement
An organization wants to calculate the average salary of employees who do not belong to the HR department.
Employee Data
| Employee | Department | Salary |
|---|---|---|
| Amit | IT | 35000 |
| Neha | HR | 30000 |
| Rahul | Sales | 45000 |
| Priya | IT | 40000 |
| Karan | HR | 28000 |
| Simran | Finance | 50000 |
Excel Formula
=AVERAGEIF(B2:B7,"<>HR",C2:C7)
Expected Result
<text color=”success” weight=”semibold”>Average Salary = ₹42,500</text>
Explanation
The condition "<>HR" means the department must not be HR.
Excel selects salaries from IT, Sales, and Finance:
- ₹35,000
- ₹45,000
- ₹40,000
- ₹50,000
Average:
[
\frac{35000+45000+40000+50000}{4}=42500
]
Concept Covered: Using the not-equal-to condition.
Student Practice Tasks
Solve these tasks using only AVERAGEIF.
- Calculate average sales for the South region.
- Calculate the average salary of employees in the Sales department.
- Calculate the average marks of students who scored at least 70.
- Calculate average Laptop revenue.
- Calculate average sales of transactions below ₹20,000.
Final Student Challenge: Build an Employee Salary Analysis Report
An organization maintains the following employee records.
Employee Data
| Employee ID | Employee | Department | Region | Salary |
|---|---|---|---|---|
| E101 | Amit | IT | North | 35000 |
| E102 | Neha | HR | South | 30000 |
| E103 | Rahul | Sales | North | 45000 |
| E104 | Priya | IT | East | 40000 |
| E105 | Karan | HR | North | 28000 |
| E106 | Simran | Sales | South | 50000 |
| E107 | Rohit | IT | North | 38000 |
| E108 | Ankit | Finance | East | 42000 |
| E109 | Aman | HR | South | 31000 |
| E110 | Arjun | Sales | North | 46000 |
Challenge Requirements
Prepare the following reports:
1. Department-Wise Average Salary
Calculate the average salary of IT, HR, Sales, and Finance employees separately.
2. Region-Wise Average Salary
Calculate the average salary of employees working in North, South, and East.
3. Salary-Based Average
Calculate the average salary of employees earning more than ₹35,000.
4. Department Exclusion
Calculate the average salary of employees who do not belong to HR.
Try to complete all four reports using AVERAGEIF.
Key Takeaways
- AVERAGEIF calculates an average based on one condition.
- It first checks the criteria range and identifies matching records.
- It calculates the average using the corresponding numerical values.
- It supports text, numerical, and comparison criteria.
- You can use cell references to make reports flexible.
- It is useful for salary analysis, sales reports, student marks, and business data.
Frequently Asked Questions (FAQs)
1. What is the AVERAGEIF function in Excel?
AVERAGEIF calculates the average of numbers that satisfy a specified condition.
2. What is the syntax of AVERAGEIF?
The syntax is =AVERAGEIF(range, criteria, [average_range]).
3. Can AVERAGEIF calculate department-wise average salary?
Yes. You can check the Department column and calculate the average using the Salary column.
4. What happens if no records match the condition?
Excel returns #DIV/0! because there are no matching numerical values to average.
5. Can AVERAGEIF use greater-than and less-than conditions?
Yes. You can use conditions such as ">30000" and "<40000".
6. Is average_range compulsory?
No. It is optional. If omitted, Excel averages the matching numeric cells in the criteria range itself.
7. Can AVERAGEIF use a cell reference as criteria?
Yes. You can enter the condition in another cell and use that cell reference in the formula.
8. Can AVERAGEIF calculate an average using multiple conditions?
No. AVERAGEIF is designed for one condition. It checks one criterion when selecting records.

