ASSIGNMENT – 3 – ANSWER KEY

SUM, AVERAGE, NESTED IF, COUNTA, COUNTIF, SUMIF & VLOOKUP

Completed Lookup Table

Subject 1st 2nd 3rd Total Average Grade
ENGLISH 24 18 22 64 21.33 B
HINDI 16 21 19 56 18.67 B
MATH 28 25 24 77 25.67 A
SCIENCE 19 17 21 57 19.00 B
SOCIAL SCIENCE 22 20 18 60 20.00 B
COMPUTER 26 23 27 76 25.33 A
GK 15 18 16 49 16.33 C
EVS 21 24 20 65 21.67 B
DRAWING 18 16 22 56 18.67 B
SANSKRIT 25 22 19 66 22.00 B

Q.1 SUM Formula

Assuming:

  • B = 1st

  • C = 2nd

  • D = 3rd

  • E = Total

In E2:

=SUM(B2:D2)

Copy down to E11.

Answers

  • ENGLISH = 64

  • HINDI = 56

  • MATH = 77

  • SCIENCE = 57

  • SOCIAL SCIENCE = 60

  • COMPUTER = 76

  • GK = 49

  • EVS = 65

  • DRAWING = 56

  • SANSKRIT = 66

Q.2 AVERAGE Formula

In F2:

=AVERAGE(B2:D2)

Copy down to F11.

Answers

  • ENGLISH = 21.33

  • HINDI = 18.67

  • MATH = 25.67

  • SCIENCE = 19.00

  • SOCIAL SCIENCE = 20.00

  • COMPUTER = 25.33

  • GK = 16.33

  • EVS = 21.67

  • DRAWING = 18.67

  • SANSKRIT = 22.00

Q.3 COUNTA Formula

To count the total number of subjects:

=COUNTA(A2:A11)

Answer:

10 Subjects

Q.4 COUNTIF – 1st Paper > 20

Formula:

=COUNTIF(B2:B11,">20")

Answer:

5 Subjects

Subjects:

  • ENGLISH – 24

  • MATH – 28

  • SOCIAL SCIENCE – 22

  • COMPUTER – 26

  • EVS – 21

  • SANSKRIT – 25

Correction: There are actually 6 subjects, because SANSKRIT also has 25.

Final Answer: 6

Q.5 COUNTIF – 2nd Paper < 20

Formula:

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

Answer:

5 Subjects

Subjects:

  • ENGLISH – 18

  • SCIENCE – 17

  • GK – 18

  • DRAWING – 16

Correction: There are actually 4 subjects.

Final Answer: 4

Q.6 NESTED IF – Grade

In G2:

=IF(F2>22,"A",IF(F2>18,"B","C"))

Copy down to G11.

Grade Answers

Subject Average Grade
ENGLISH 21.33 B
HINDI 18.67 B
MATH 25.67 A
SCIENCE 19.00 B
SOCIAL SCIENCE 20.00 B
COMPUTER 25.33 A
GK 16.33 C
EVS 21.67 B
DRAWING 18.67 B
SANSKRIT 22.00 B

Q.7 VLOOKUP – MATH

Lookup range:

$A$2:$G$11

Total Marks

=VLOOKUP("MATH",$A$2:$G$11,5,FALSE)

Answer: 77

Average

=VLOOKUP("MATH",$A$2:$G$11,6,FALSE)

Answer: 25.67

Grade

=VLOOKUP("MATH",$A$2:$G$11,7,FALSE)

Answer: A

Q.8 VLOOKUP – COMPUTER

Total Marks

=VLOOKUP("COMPUTER",$A$2:$G$11,5,FALSE)

Answer: 76

Average

=VLOOKUP("COMPUTER",$A$2:$G$11,6,FALSE)

Answer: 25.33

Grade

=VLOOKUP("COMPUTER",$A$2:$G$11,7,FALSE)

Answer: A

Q.9 SUMIF – Total Marks of Grade A

Formula:

=SUMIF(G2:G11,"A",E2:E11)

Grade A subjects:

  • MATH = 77

  • COMPUTER = 76

Answer:

153 Marks

Q.10 VLOOKUP – ENGLISH, SCIENCE & SANSKRIT

ENGLISH

Total

=VLOOKUP("ENGLISH",$A$2:$G$11,5,FALSE)

64

Average

=VLOOKUP("ENGLISH",$A$2:$G$11,6,FALSE)

21.33

Grade

=VLOOKUP("ENGLISH",$A$2:$G$11,7,FALSE)

B

SCIENCE

Total

=VLOOKUP("SCIENCE",$A$2:$G$11,5,FALSE)

57

Average

=VLOOKUP("SCIENCE",$A$2:$G$11,6,FALSE)

19.00

Grade

=VLOOKUP("SCIENCE",$A$2:$G$11,7,FALSE)

B

SANSKRIT

Total

=VLOOKUP("SANSKRIT",$A$2:$G$11,5,FALSE)

66

Average

=VLOOKUP("SANSKRIT",$A$2:$G$11,6,FALSE)

22.00

Grade

=VLOOKUP("SANSKRIT",$A$2:$G$11,7,FALSE)

B

Final Answers at a Glance

Question Answer
Q.3 Total Subjects 10
Q.4 1st Marks > 20 6
Q.5 2nd Marks < 20 4
Q.7 MATH 77, 25.67, A
Q.8 COMPUTER 76, 25.33, A
Q.9 Total Grade A Marks 153
Q.10 ENGLISH 64, 21.33, B
Q.10 SCIENCE 57, 19.00, B
Q.10 SANSKRIT 66, 22.00, B
PreviousNext