ASSIGNMENT – 23
AVERAGEIF — Answer Key
Table 1: Product-wise Units Sold
Data Range: A2:B13
| Product | 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 |
Q.1 Average Units Sold of Product A
=AVERAGEIF(A2:A13,"A",B2:B13)Calculation:
(180 + 260 + 220) ÷ 3 = 220
Answer: 220 units
Q.2 Average Units Sold of Product D
=AVERAGEIF(A2:A13,"D",B2:B13)Calculation:
(450 + 150) ÷ 2 = 300
Answer: 300 units
Q.3 Average Units Sold of Product B
=AVERAGEIF(A2:A13,"B",B2:B13)Calculation:
(75 + 40 + 95) ÷ 3 = 70
Answer: 70 units
Table 2: Student Scores
Data Range: D2:F11
| 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 |
Q.4 Average Score of Second Semester
=AVERAGEIF(E2:E11,"Second",F2:F11)Calculation:
(76 + 84 + 88 + 65) ÷ 4 = 78.25
Answer: 78.25
Q.5 Average Score of Third Semester
=AVERAGEIF(E2:E11,"Third",F2:F11)Calculation:
(91 + 72 + 79) ÷ 3 = 80.67
Answer: 80.67
Table 3: Zone-wise Sales
Data Range: G2:I13
| 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 |
Q.6 Average Sales of North Zone
=AVERAGEIF(G2:G13,"North",I2:I13)Calculation:
(8,500 + 7,200 + 9,500) ÷ 3 = 8,400
Answer: ₹8,400
Q.7 Average Sales of West Zone
=AVERAGEIF(G2:G13,"West",I2:I13)Calculation:
(18,000 + 12,400 + 20,000) ÷ 3 = 16,800
Answer: ₹16,800
Q.8 Average Units Sold Greater Than 200
=AVERAGEIF(B2:B13,">200",B2:B13)Values greater than 200:
260, 450, 310, 220, 280
Calculation:
(260 + 450 + 310 + 220 + 280) ÷ 5
= 1,520 ÷ 5
= 304
Answer: 304 units
Q.9 Average Units Sold Less Than 100
=AVERAGEIF(B2:B13,"<100",B2:B13)Values less than 100:
75, 40, 80, 95
Calculation:
(75 + 40 + 80 + 95) ÷ 4
= 290 ÷ 4
= 72.5
Answer: 72.5 units
Q.10 Average Sales of South and East Zones
South Zone
=AVERAGEIF(G2:G13,"South",I2:I13)Calculation:
(15,000 + 9,800 + 16,500) ÷ 3
= 13,766.67
South Zone Average = ₹13,766.67
East Zone
=AVERAGEIF(G2:G13,"East",I2:I13)Calculation:
(11,500 + 13,600 + 14,200) ÷ 3
= 13,100
East Zone Average = ₹13,100
Final Answer Summary
| Q. | Question | Answer |
|---|---|---|
| Q1 | Average Units – Product A | 220 |
| Q2 | Average Units – Product D | 300 |
| Q3 | Average Units – Product B | 70 |
| Q4 | Average Score – Second Semester | 78.25 |
| Q5 | Average Score – Third Semester | 80.67 |
| Q6 | Average Sales – North Zone | ₹8,400 |
| Q7 | Average Sales – West Zone | ₹16,800 |
| Q8 | Average Units > 200 | 304 |
| Q9 | Average Units < 100 | 72.5 |
| Q10 | South Zone Average | ₹13,766.67 |
| Q10 | East Zone Average | ₹13,100 |
⭐ Key Learning
AVERAGEIF is used when we want to calculate an average based on one condition.
Syntax:
=AVERAGEIF(range,criteria,average_range)Example:
=AVERAGEIF(A2:A13,"A",B2:B13)This means: Find Product A → take its Units Sold → calculate their average.