Excel-AVERAGEIF, AVERAGEIFS, MAXIFS and Conditional Functions Practice Questions with Solutions

Introduction

Excel conditional functions help you analyze data based on specific conditions. In this chapter, you will practice AVERAGEIF, AVERAGEIFS, MAXIFS, and MINIFS with practical examples. You will learn how to calculate conditional averages, find the highest and lowest values that meet specific conditions, and combine multiple criteria. The questions gradually increase in difficulty so you can build confidence through practice. Excel-AVERAGEIF AVERAGEIFS MAXIFS and Conditional Functions practice questions with solutions to help you understand the concepts.

Q1. Find the Average Marks of Students Who Scored Above 70

Problem Statement

You have student marks. Use AVERAGEIF to calculate the average marks of students who scored more than 70.

Sample Data

Excel Formula

=AVERAGEIF(B2:B7,">70")

Output

82.33

Explanation

AVERAGEIF calculates the average of cells that meet one condition.

The students scoring above 70 are:

82
75
90

Therefore:

(82 + 75 + 90) / 3 = 82.33

Concept Covered

• AVERAGEIF
• Conditional average
• Comparison criteria

Q2. Find the Average Salary of IT Employees

Problem Statement

Use AVERAGEIF to calculate the average salary of employees working in the IT department.

Sample Data

Excel Formula

=AVERAGEIF(B2:B7,"IT",C2:C7)

Output

58333.33

Explanation

The formula checks the Department column for IT and calculates the average of the corresponding salaries.

55000 + 62000 + 58000 = 175000

175000 / 3 = 58333.33

Concept Covered

• AVERAGEIF
• Text criteria
• Average based on another column

Q3. Find the Average Sales for Delhi

Problem Statement

Calculate the average sales made in Delhi using AVERAGEIF.

Sample Data

Excel Formula

=AVERAGEIF(B2:B7,"Delhi",C2:C7)

Output

26000

Explanation

Delhi sales are:

25000
18000
35000

Average:

(25000 + 18000 + 35000) / 3
= 26000

Concept Covered

• AVERAGEIF
• Criteria range
• Average range
• Sales analysis

Q4. Find the Average Sales for Delhi by Product

Problem Statement

Use AVERAGEIFS to calculate the average sales of laptops sold in Delhi.

Sample Data

Excel Formula

=AVERAGEIFS(D2:D7,B2:B7,"Delhi",C2:C7,"Laptop")

Output

56666.67

Explanation

Two conditions are applied:

City = Delhi
Product = Laptop

Matching sales:

55000
58000
57000

Average:

(55000 + 58000 + 57000) / 3
= 56666.67

Concept Covered

• AVERAGEIFS
• Multiple criteria
• Conditional average
• AND logic

Q5. Find the Average Salary of IT Employees Earning More Than ₹50,000

Problem Statement

Use AVERAGEIFS to calculate the average salary of employees who:

• Work in IT
• Earn more than ₹50,000

Sample Data

Excel Formula

=AVERAGEIFS(C2:C7,B2:B7,"IT",C2:C7,">50000")

Output

59000

Explanation

The formula checks both conditions:

Department = IT
Salary > 50000

Matching salaries:

55000
70000
52000

Average:

(55000 + 70000 + 52000) / 3
= 59000

Concept Covered

• AVERAGEIFS
• Multiple conditions
• Numeric criteria

Q6. Find the Highest Sales Made in Delhi

Problem Statement

Use MAXIFS to find the highest sales amount from Delhi.

Sample Data

Excel Formula

=MAXIFS(C2:C7,B2:B7,"Delhi")

Output

35000

Explanation

MAXIFS returns the largest value that meets a condition.

Delhi sales are:

25000
18000
35000

The highest value is:

35000

Concept Covered

• MAXIFS
• Conditional maximum
• Sales analysis

Q7. Find the Lowest Salary in the IT Department

Problem Statement

Use MINIFS to find the lowest salary among IT employees.

Sample Data

Excel Formula

=MINIFS(C2:C7,B2:B7,"IT")

Output

55000

Explanation

IT salaries are:

55000
62000
58000

The smallest salary is:

55000

Concept Covered

• MINIFS
• Conditional minimum
• Department analysis

Q8. Find the Highest Laptop Sale in Delhi

Problem Statement

Use MAXIFS to find the highest laptop sale made in Delhi.

Sample Data

Excel Formula

=MAXIFS(D2:D7,B2:B7,"Delhi",C2:C7,"Laptop")

Output

58000

Explanation

Two conditions are applied:

City = Delhi
Product = Laptop

Matching sales:

55000
58000
57000

The highest value is:

58000

Concept Covered

• MAXIFS
• Multiple conditions
• Conditional maximum

Q9. Find the Lowest Laptop Sale in Delhi

Problem Statement

Use MINIFS to find the lowest laptop sale made in Delhi.

Sample Data

Excel Formula

=MINIFS(D2:D7,B2:B7,"Delhi",C2:C7,"Laptop")

Output

55000

Explanation

The formula looks only at rows where:

City = Delhi
Product = Laptop

The matching values are:

55000
58000
57000

The lowest value is:

55000

Concept Covered

• MINIFS
• Multiple criteria
• Conditional minimum

Q10. Create a Conditional Sales Analysis

Problem Statement

Use a combination of AVERAGEIFS, MAXIFS, and MINIFS to analyze laptop sales made in Delhi.

Sample Data

1. Calculate Average Laptop Sales in Delhi

=AVERAGEIFS(D2:D9,B2:B9,"Delhi",C2:C9,"Laptop")

Output

58000

2. Find the Highest Laptop Sale in Delhi

=MAXIFS(D2:D9,B2:B9,"Delhi",C2:C9,"Laptop")

Output

62000

3. Find the Lowest Laptop Sale in Delhi

=MINIFS(D2:D9,B2:B9,"Delhi",C2:C9,"Laptop")

Output

55000

Explanation

All three formulas use the same conditions:

City = Delhi
Product = Laptop

But each function performs a different calculation:

AVERAGEIFS → Average matching values
MAXIFS     → Highest matching value
MINIFS     → Lowest matching value

This is a useful pattern for building a small Excel sales analysis report.

Key Takeaways

• AVERAGEIF calculates an average using one condition.
• AVERAGEIFS calculates an average using multiple conditions.
• MAXIFS finds the highest value that meets specified conditions.
• MINIFS finds the lowest value that meets specified conditions.
• AVERAGEIFS, MAXIFS, and MINIFS are useful for analyzing data using multiple criteria.
• Text criteria such as "Delhi" or "IT" can be used with these functions.
• Comparison criteria such as ">50000" can be used for numerical analysis.
• AVERAGEIF uses one condition, while AVERAGEIFS supports multiple conditions.
• MAXIFS and MINIFS are useful when you need the highest or lowest value from a filtered set of data.
• These functions are useful for sales reports, employee analysis, student marks, inventory, and other business datasets.

FAQs

1. What is the difference between AVERAGEIF and AVERAGEIFS?

AVERAGEIF calculates an average using one condition, while AVERAGEIFS can calculate an average using multiple conditions.

2. What does MAXIFS do in Excel?

MAXIFS returns the largest value from a range that satisfies one or more specified conditions.

3. What does MINIFS do in Excel?

MINIFS returns the smallest value from a range that satisfies one or more specified conditions.

4. Can AVERAGEIFS use multiple conditions?

Yes. For example, you can calculate the average sales where the city is Delhi and the product is Laptop.

=AVERAGEIFS(D2:D20,B2:B20,"Delhi",C2:C20,"Laptop")

5. Can MAXIFS and MINIFS use multiple conditions?

Yes. Both functions can use multiple criteria. For example, you can find the highest or lowest laptop sale made in a particular city.

6. What is the difference between MAX and MAXIFS in Excel?

MAX finds the largest value in a range without applying a condition. MAXIFS finds the largest value that meets specified conditions.

7. What is the difference between MIN and MINIFS?

MIN finds the smallest value in a range without applying conditions. MINIFS finds the smallest value that meets specified conditions.

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar