Mixed Basic Excel Functions and Practical Data Analysis Practice Questions with Solutions

Introduction

Excel becomes more useful when you combine multiple functions to solve practical data problems. In this chapter, you will practice mixed Excel functions such as SUM, AVERAGE, COUNT, MAX, MIN, IF, COUNTIF, SUMIF, AVERAGEIF, MAXIFS, MINIFS, and IFERROR. The examples use student, employee, sales, and inventory data so you can practice choosing the right function for a real-world situation. Mixed Basic Excel Functions and Practical Data Analysis practice questions with solutions to help you understand the concepts.

Q1. Calculate Total, Average, Highest and Lowest Sales

Problem Statement

You have a list of sales amounts. Calculate:

• Total sales
• Average sales
• Highest sale
• Lowest sale

Sample Data

1. Total Sales

=SUM(B2:B7)

Output

157000

2. Average Sales

=AVERAGE(B2:B7)

Output

26166.67

3. Highest Sale

=MAX(B2:B7)

Output

35000

4. Lowest Sale

=MIN(B2:B7)

Output

18000

Explanation

Here, each basic function performs a different calculation:

SUM     → Adds all sales
AVERAGE → Calculates the average
MAX     → Finds the highest sale
MIN     → Finds the lowest sale

Concepts Covered

• SUM
• AVERAGE
• MAX
• MIN

Q2. Count Employees and Find Average Salary

Problem Statement

Calculate:

1. Total number of employees
2. Average salary
3. Highest salary
4. Lowest salary

Sample Data

1. Count Employees

=COUNTA(A2:A7)

Output

6

2. Average Salary

=AVERAGE(C2:C7)

Output

53333.33

3. Highest Salary

=MAX(C2:C7)

Output

62000

4. Lowest Salary

=MIN(C2:C7)

Output

45000

Explanation

COUNTA counts cells containing data, while AVERAGE, MAX, and MIN analyze the salary column.

Concepts Covered

• COUNTA
• AVERAGE
• MAX
• MIN
• Employee data analysis

Q3. Calculate Student Percentage and Result

Problem Statement

Calculate the percentage of each student and display:

• "Pass" if the percentage is 40% or more
• "Fail" if the percentage is below 40%

Sample Data

Percentage Formula

In D2:

=B2/C2*100

Result Formula

In E2:

=IF(D2>=40,"Pass","Fail")

Copy both formulas down.

Output

Explanation

The percentage is calculated first.

For Rahul:

450 ÷ 500 × 100 = 90%

Then IF checks whether the percentage is at least 40.

Concepts Covered

• Division
• Percentage calculation
• IF
• Conditional analysis

Q4. Find Total Sales for a Particular City

Problem Statement

Use SUMIF to calculate the total sales made in Delhi.

Sample Data

Excel Formula

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

Output

78000

Explanation

The formula looks for "Delhi" in the City column and adds the corresponding sales.

25000 + 18000 + 35000 = 78000

Concepts Covered

• SUMIF
• Criteria
• Conditional totals
• Sales analysis

Q5. Calculate Average Sales for a Specific Product

Problem Statement

Calculate the average sales of laptops using AVERAGEIF.

Sample Data

Excel Formula

=AVERAGEIF(A2:A7,"Laptop",B2:B7)

Output

58333.33

Explanation

Laptop sales are:

55000
58000
62000

Therefore:

(55000 + 58000 + 62000) / 3
= 58333.33

Concepts Covered

• AVERAGEIF
• Conditional average
• Product analysis

Q6. Count Sales Records Above ₹30,000

Problem Statement

Use COUNTIF to count how many sales transactions are greater than ₹30,000.

Sample Data

Excel Formula

=COUNTIF(B2:B8,">30000")

Output

3

Explanation

The sales above ₹30,000 are:

42000
35000
50000

Therefore, the count is:

3

Concepts Covered

• COUNTIF
• Comparison criteria
• Sales analysis

Q7. Find the Highest and Lowest Sales for Delhi

Problem Statement

Use MAXIFS and MINIFS to find the highest and lowest sales made in Delhi.

Sample Data

Highest Delhi Sale

=MAXIFS(C2:C8,B2:B8,"Delhi")

Output

42000

Lowest Delhi Sale

=MINIFS(C2:C8,B2:B8,"Delhi")

Output

18000

Explanation

The Delhi sales are:

25000
18000
35000
42000

Therefore:

Highest = 42000
Lowest  = 18000

Concepts Covered

• MAXIFS
• MINIFS
• Multiple-condition analysis
• Sales data

Q8. Calculate Employee Bonus Using IF and AND

Problem Statement

An employee receives a "Bonus" if:

• Sales are at least ₹50,000
• Rating is at least 4

Otherwise, display "No Bonus".

Sample Data

Excel Formula

=IF(AND(B2>=50000,C2>=4),"Bonus","No Bonus")

Copy the formula down.

Output

Explanation

AND requires both conditions to be true.

For Rahul:

Sales >= 50000 → TRUE
Rating >= 4 → TRUE

Therefore:

Bonus

For Amit:

Sales >= 50000 → TRUE
Rating >= 4 → FALSE

Both conditions are not true, so:

No Bonus

Concepts Covered

• IF
• AND
• Multiple conditions
• Employee analysis

Q9. Create a Sales Performance Category

Problem Statement

Classify employees based on their sales:

• ₹50,000 or more → "Excellent"
• ₹30,000 or more → "Good"
• ₹20,000 or more → "Average"
• Below ₹20,000 → "Needs Improvement"

Sample Data

Excel Formula

=IF(B2>=50000,"Excellent",IF(B2>=30000,"Good",IF(B2>=20000,"Average","Needs Improvement")))

Output

Explanation

Excel checks the conditions from left to right.

For Rahul:

55000 >= 50000

So the result is:

Excellent

For Amit:

28000 >= 50000 → FALSE
28000 >= 30000 → FALSE
28000 >= 20000 → TRUE

Therefore:

Average

Concepts Covered

• Nested IF
• Multiple conditions
• Sales classification
• Practical data analysis

Q10. Build a Mini Sales Analysis Report

Problem Statement

Create a practical sales summary using multiple Excel functions.

Calculate:

1. Total sales
2. Average sales
3. Number of sales transactions
4. Sales above ₹30,000
5. Delhi sales
6. Highest sale
7. Lowest sale
8. Sales performance status

Sample Data

1. Total Sales

=SUM(C2:C9)

Output

309000

2. Average Sales

=AVERAGE(C2:C9)

Output

38625

3. Number of Sales Transactions

=COUNT(C2:C9)

Output

8

4. Sales Above ₹30,000

=COUNTIF(C2:C9,">30000")

Output

5

5. Total Delhi Sales

=SUMIF(B2:B9,"Delhi",C2:C9)

Output

186000

6. Highest Sale

=MAX(C2:C9)

Output

65000

7. Lowest Sale

=MIN(C2:C9)

Output

18000

8. Performance Status

In D2:

=IF(C2>=50000,"Excellent",IF(C2>=30000,"Good",IF(C2>=20000,"Average","Needs Improvement")))

Copy the formula down.

Final Summary

Explanation

This example combines several functions into one practical analysis:

SUM       → Total sales
AVERAGE   → Average sales
COUNT     → Number of transactions
COUNTIF   → Transactions above a condition
SUMIF     → Sales for a particular city
MAX       → Highest sale
MIN       → Lowest sale
IF        → Performance classification

This type of combination is useful when creating a basic Excel sales report or dashboard.

Key Takeaways

• Excel becomes more powerful when multiple functions are combined.
• SUM, AVERAGE, COUNT, MAX, and MIN are essential functions for basic data analysis.
• IF can classify data based on conditions.
• COUNTIF counts values that meet a condition.
• SUMIF calculates totals based on a condition.
• AVERAGEIF calculates averages based on a condition.
• MAXIFS and MINIFS find the highest and lowest values matching conditions.
• AND can be combined with IF when multiple conditions must be true.
• Nested IF can create multiple performance categories.
• Choosing the right function starts with understanding what the question is asking you to calculate.
• Combining functions is an important skill for practical Excel work and reporting.

FAQs

1. What are the most important Excel functions for basic data analysis?

Some commonly used functions are SUM, AVERAGE, COUNT, MAX, MIN, IF, COUNTIF, and SUMIF.

2. What is the difference between COUNT and COUNTA?

COUNT counts cells containing numbers, while COUNTA counts cells containing any type of data, including text.

3. When should I use SUMIF?

Use SUMIF when you need to add values that meet a specific condition.

For example:

=SUMIF(B2:B20,"Delhi",C2:C20)

4. When should I use COUNTIF?

Use COUNTIF when you need to count how many cells meet a specific condition.

Example:

=COUNTIF(C2:C20,">50000")

5. Can multiple Excel functions be used together?

Yes. Functions can be combined to solve practical problems. For example, IF and AND can be combined to evaluate multiple conditions.

=IF(AND(B2>=50000,C2>=4),"Bonus","No Bonus")

6. What is a nested IF formula?

A nested IF formula places one IF function inside another. It can be used when there are multiple possible conditions or categories.

7. How do I choose the right Excel function?

Start by identifying the required operation. If you need a total, use SUM; for an average, use AVERAGE; for counting, use COUNT or COUNTIF; for conditional totals, use SUMIF; and for conditional maximum or minimum values, use MAXIFS or MINIFS.

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

Rishabh Kumar