ASSIGNMENT – 3

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

Main Lookup Table

Subject 1st 2nd 3rd Total Average Grade
ENGLISH 24 18 22      
HINDI 16 21 19      
MATH 28 25 24      
SCIENCE 19 17 21      
SOCIAL SCIENCE 22 20 18      
COMPUTER 26 23 27      
GK 15 18 16      
EVS 21 24 20      
DRAWING 18 16 22      
SANSKRIT 25 22 19      

Questions

Q.1 SUM Formula

Calculate the Total Marks of each subject using the SUM formula.

Q.2 AVERAGE Formula

Calculate the Average Marks of each subject using the AVERAGE formula.

Q.3 COUNTA Formula

Using COUNTA, calculate the total number of subjects in the list.

Q.4 COUNTIF Formula

Using COUNTIF, calculate how many subjects have 1st Paper marks greater than 20 (>20).

Q.5 COUNTIF Formula

Using COUNTIF, calculate how many subjects have 2nd Paper marks less than 20 (<20).

Q.6 NESTED IF Formula

Using NESTED IF, assign grades according to the Average Marks:

Average Grade
Greater than 22 A
Greater than 18 B
Otherwise C

Q.7 VLOOKUP – MATH

Using VLOOKUP, find the following details for MATH:

  • Total Marks

  • Average Marks

  • Grade

Q.8 VLOOKUP – COMPUTER

Using VLOOKUP, find the following details for COMPUTER:

  • Total Marks

  • Average Marks

  • Grade

Q.9 SUMIF – Grade A

Using SUMIF, calculate the total marks of all subjects whose Grade is “A”.

Q.10 VLOOKUP – Multiple Subjects

Using VLOOKUP, find the following details for:

  • ENGLISH

  • SCIENCE

  • SANSKRIT

For each subject, find:

  • Total Marks

  • Average Marks

  • Grade

VLOOKUP Lookup Range

For all VLOOKUP questions, use the complete table from Subject to Grade as the lookup range:

A2

The Subject column will be the lookup column.

Example:

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

This returns the Total Marks of MATH.

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

This returns the Average Marks of MATH.

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

This returns the Grade of MATH.

PreviousNext