ASSIGNMENT – 8

Student Marks & Grade

Use of Formulas – SUM, AVERAGE, COUNTA, COUNTIF, SUMIF & Nested IF

Student Marks Table

Sr. No.	Name	Maths	English	Science	Total	Percentage	Grade
1	ARYAN	85	78	92
2	RIYA	55	65	70
3	KUNAL	72	68	75
4	NEHA	90	88	95
5	AMAN	35	42	38
6	PRIYA	60	55	72
7	ROHAN	48	58	65
8	SIMRAN	80	75	85
9	VIKAS	25	35	30
10	POOJA	68	72	60

Questions

Q.1 COUNTA Formula

How many students are there in the list?

Use the COUNTA formula.

Q.2 SUM Formula – Total Marks

Calculate the Total Marks of all students using the SUM formula.

Total Marks = Maths + English + Science

Q.3 AVERAGE Formula – Percentage

Calculate the Percentage of each student using the AVERAGE formula.

Since each subject is out of 100, the average of the three subjects will represent the percentage.

Percentage = AVERAGE(Maths, English, Science)

Q.4 COUNTIF Formula

Using COUNTIF, calculate how many students have a Percentage greater than 60%.

Q.5 SUMIF Formula

Using SUMIF, calculate the Total Marks of:

1. RIYA

2. PRIYA

Q.6 Nested IF – Grade

Using the Nested IF formula, assign grades according to Percentage:

Percentage	Grade
Greater than 75	EXCELLENT
Greater than 50	GOOD
Otherwise	NEEDS IMPROVEMENT

Use the following logic:

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

Q.7 COUNTIF – EXCELLENT

Using COUNTIF, calculate how many students are EXCELLENT.

Q.8 COUNTIF – GOOD

Using COUNTIF, calculate how many students are GOOD.

Q.9 COUNTIF – NEEDS IMPROVEMENT

Using COUNTIF, calculate how many students are NEEDS IMPROVEMENT.

Q.10 SUMIF – EXCELLENT Students

Using SUMIF, calculate the Total Marks of all students whose Grade is “EXCELLENT”.

Formulas to Practice

SUM | AVERAGE | COUNTA | COUNTIF | SUMIF | Nested IF

Important Excel Formulas

Total Marks:

=SUM(C2:E2)

Percentage:

=AVERAGE(C2:E2)

Grade:

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