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