ASSIGNMENT – 23

Use of Formula – AVERAGEIF

Table 1 – Product-wise Units Sold

PRODUCT NAME UNITS SOLD
A 180
B 75
C 120
A 260
D 450
B 40
E 310
C 80
D 150
A 220
B 95
E 280

Table 2 – Student Scores

STUDENT SEMESTER SCORE
Aarav First 82
Riya Second 76
Karan First 68
Neha Third 91
Mohit Second 84
Priya Third 72
Ananya Second 88
Rahul First 74
Simran Third 79
Vikas Second 65

Table 3 – Zone-wise Sales

ZONE CITY SALES
North Delhi 8,500
South Chennai 15,000
East Patna 11,500
West Pune 18,000
North Jaipur 7,200
South Mangalore 9,800
East Ranchi 13,600
West Surat 12,400
North Chandigarh 9,500
South Hyderabad 16,500
East Kolkata 14,200
West Mumbai 20,000

Questions

Q.1 Find the Average Units Sold of Product A.

Use: AVERAGEIF

Q.2 Find the Average Units Sold of Product D.

Use: AVERAGEIF

Q.3 Find the Average Units Sold of Product B.

Use: AVERAGEIF

Q.4 Find the Average Score of students from the Second Semester.

Use: AVERAGEIF

Q.5 Find the Average Score of students from the Third Semester.

Use: AVERAGEIF

Q.6 Find the Average Sales of the North Zone.

Use: AVERAGEIF

Q.7 Find the Average Sales of the West Zone.

Use: AVERAGEIF

Q.8 Find the Average Units Sold where Units Sold are greater than 200.

Use: AVERAGEIF

Q.9 Find the Average Units Sold where Units Sold are less than 100.

Use: AVERAGEIF

Q.10 Find the Average Sales of the South Zone and East Zone separately using AVERAGEIF.

Formula Examples

1. Average Units Sold of Product A

=AVERAGEIF(A2:A13,"A",B2:B13)

2. Average Score of Second Semester

If the Student Scores table is in columns D:

=AVERAGEIF(E2:E11,"Second",F2:F11)

3. Average Units Sold Greater Than 200

=AVERAGEIF(B2:B13,">200",B2:B13)

4. Average Sales of North Zone

If the Zone-wise Sales table is in columns G:

=AVERAGEIF(G2:G13,"North",I2:I13)

AVERAGEIF Syntax

=AVERAGEIF(range,criteria,average_range)

Meaning

  • range → Range where the condition is checked

  • criteria → Condition to apply

  • average_range → Range from which the average is calculated

Learning Objective

After completing this assignment, students will learn how to:

  • Calculate an average based on a specific condition.

  • Use AVERAGEIF with text criteria.

  • Use AVERAGEIF with numerical conditions such as >200 and <100.

  • Apply AVERAGEIF to product, student and sales data.

PreviousNext