Excel Practice Assignment – Answer Sheet

Solved Data Table

Roll No Student Name Hindi English Math Physics Chemistry Total Average Grade
1 RAM 20 10 14 18 15 77 15.4 A
2 ASHOK 21 12 14 12 18 77 15.4 A
3 MANOJ 33 15 7 14 17 86 17.2 A
4 RAJESH 15 14 8 16 20 73 14.6 B
5 RANJANA 14 17 10 13 18 72 14.4 B
6 POOJA 16 8 20 17 15 76 15.2 A
7 MAHESH 18 19 3 10 14 64 12.8 B
8 ASHUTOSH 19 20 7 14 18 78 15.6 A
9 ANIL 22 13 8 12 19 74 14.8 B
10 PREM 26 12 10 11 27 86 17.2 A

Answers

Q.1 Total Marks Formula

Formula in H2:

=SUM(C2:G2)

Drag down to calculate all students.

Answers:
RAM = 77
ASHOK = 77
MANOJ = 86
RAJESH = 73
RANJANA = 72
POOJA = 76
MAHESH = 64
ASHUTOSH = 78
ANIL = 74
PREM = 86

Q.2 Average Marks Formula

Formula in I2:

=AVERAGE(C2:G2)

Drag down to calculate all students.

Answers:
RAM = 15.4
ASHOK = 15.4
MANOJ = 17.2
RAJESH = 14.6
RANJANA = 14.4
POOJA = 15.2
MAHESH = 12.8
ASHUTOSH = 15.6
ANIL = 14.8
PREM = 17.2

Q.3 Grade Formula using IF

Condition: Average > 15 = A, otherwise B.

Formula in J2:

=IF(I2>15,"A","B")

Drag down to calculate all grades.

Result:

  • Grade A = 6 students
  • Grade B = 4 students

Note: आपने यहाँ Average > 15 दिया है, इसलिए >15 ही लगेगा, >=15 नहीं।

Q.4 Count Grade A using COUNTIF

Formula:

=COUNTIF(J2:J11,"A")

Answer = 6

Q.5 Count Grade B using COUNTIF

Formula:

=COUNTIF(J2:J11,"B")

Answer = 4

Q.6 Total Marks of Ashok and Manoj using SUMIF

Ashok Total Marks:

=SUMIF(B2:B11,"ASHOK",H2:H11)

Answer = 77

Manoj Total Marks:

=SUMIF(B2:B11,"MANOJ",H2:H11)

Answer = 86

Combined Total:

=SUMIF(B2:B11,"ASHOK",H2:H11)+SUMIF(B2:B11,"MANOJ",H2:H11)

Answer = 163

Q.7 Average Marks in Hindi using AVERAGE

Formula:

=AVERAGE(C2:C11)

Answer = 20.4

Q.8 Total Number of Students using COUNTA

Formula:

=COUNTA(B2:B11)

Answer = 10

Q.9 Students scoring more than 20 in Hindi

Formula:

=COUNTIF(C2:C11,">20")

Answer = 4

Students are:

  • ASHOK – 21
  • MANOJ – 33
  • ANIL – 22
  • PREM – 26

Q.10 Hindi > 20 AND English < 15 using COUNTIFS

Formula:

=COUNTIFS(C2:C11,">20",D2:D11,"<15")

Answer = 3

Students satisfying both conditions:

Student Hindi English
ASHOK 21 12
ANIL 22 13
PREM 26 12

Final Answer = 3

Final Answer Key

Q. No. Formula/Function Answer
1 SUM Total marks calculated
2 AVERAGE Average calculated
3 IF 6 A, 4 B
4 COUNTIF 6
5 COUNTIF 4
6 SUMIF Ashok 77, Manoj 86, Total 163
7 AVERAGE 20.4
8 COUNTA 10
9 COUNTIF 4
10 COUNTIFS 3

 

PreviousNext