ASSIGNMENT – 8 – ANSWER KEY

Student Marks & Grade

Completed Student Marks Table

Sr. No. Name Maths English Science Total Percentage Grade
1 ARYAN 85 78 92 255 85.00% EXCELLENT
2 RIYA 55 65 70 190 63.33% GOOD
3 KUNAL 72 68 75 215 71.67% GOOD
4 NEHA 90 88 95 273 91.00% EXCELLENT
5 AMAN 35 42 38 115 38.33% NEEDS IMPROVEMENT
6 PRIYA 60 55 72 187 62.33% GOOD
7 ROHAN 48 58 65 171 57.00% GOOD
8 SIMRAN 80 75 85 240 80.00% EXCELLENT
9 VIKAS 25 35 30 90 30.00% NEEDS IMPROVEMENT
10 POOJA 68 72 60 200 66.67% GOOD

Q.1 COUNTA – Total Students

Assuming names are in B2:

=COUNTA(B2:B11)

Answer: 10 Students

Q.2 SUM – Total Marks

In F2:

=SUM(C2:E2)

Copy down to F11.

Total Marks

Student Total
ARYAN 255
RIYA 190
KUNAL 215
NEHA 273
AMAN 115
PRIYA 187
ROHAN 171
SIMRAN 240
VIKAS 90
POOJA 200

Q.3 AVERAGE – Percentage

In G2:

=AVERAGE(C2:E2)

Copy down to G11.

Percentage Answers

  • ARYAN → 85.00%

  • RIYA → 63.33%

  • KUNAL → 71.67%

  • NEHA → 91.00%

  • AMAN → 38.33%

  • PRIYA → 62.33%

  • ROHAN → 57.00%

  • SIMRAN → 80.00%

  • VIKAS → 30.00%

  • POOJA → 66.67%

Q.4 COUNTIF – Percentage Greater Than 60%

Formula:

=COUNTIF(G2:G11,">60")

Students above 60%:

ARYAN, RIYA, KUNAL, NEHA, PRIYA, SIMRAN, POOJA

Answer: 7 Students

Q.5 SUMIF – Total Marks of RIYA & PRIYA

RIYA

=SUMIF(B2:B11,"RIYA",F2:F11)

Answer: 190

PRIYA

=SUMIF(B2:B11,"PRIYA",F2:F11)

Answer: 187

Combined Total

190 + 187 = 377

Q.6 Nested IF – Grade

In H2:

=IF(G2>75,"EXCELLENT",IF(G2>50,"GOOD","NEEDS IMPROVEMENT"))

Copy down to H11.

Grade Results

Grade Students
EXCELLENT ARYAN, NEHA, SIMRAN
GOOD RIYA, KUNAL, PRIYA, ROHAN, POOJA
NEEDS IMPROVEMENT AMAN, VIKAS

Q.7 COUNTIF – EXCELLENT

Formula:

=COUNTIF(H2:H11,"EXCELLENT")

Answer: 3 Students

Q.8 COUNTIF – GOOD

Formula:

=COUNTIF(H2:H11,"GOOD")

Answer: 5 Students

Q.9 COUNTIF – NEEDS IMPROVEMENT

Formula:

=COUNTIF(H2:H11,"NEEDS IMPROVEMENT")

Answer: 2 Students

Q.10 SUMIF – Total Marks of EXCELLENT Students

Formula:

=SUMIF(H2:H11,"EXCELLENT",F2:F11)

EXCELLENT students:

  • ARYAN = 255

  • NEHA = 273

  • SIMRAN = 240

Calculation:

255 + 273 + 240 = 768

Answer: 768 Marks

Final Answers

Question Answer
Q.1 Total Students 10
Q.4 Percentage > 60% 7
Q.5 RIYA Total 190
Q.5 PRIYA Total 187
Q.5 Combined Total 377
Q.7 EXCELLENT 3
Q.8 GOOD 5
Q.9 NEEDS IMPROVEMENT 2
Q.10 Total Marks of EXCELLENT Students 768
PreviousNext