ASSIGNMENT – 30

Use of Formula – NESTED IF

Topic: Student Marks & Grade Calculation

Use Nested IF to calculate the grade of each student according to the given grading criteria.

PART A – Student Marks & Grade

Enter the following data in Excel:

NAME	TOTAL MARKS	GRADE
Aarav Sharma	96
Riya Kapoor	89
Karan Mehta	83
Neha Singh	77
Rohit Verma	72
Simran Kaur	68
Mohit Jain	63
Pooja Gupta	57
Rahul Saini	48
Ananya Sharma	92

PART B – Grade Criteria

Use the following grading system:

MARKS	GRADE
90–100	A+
85–<90	A
80–<85	B+
75–<80	B
70–<75	C+
65–<70	C
60–<65	D+
50–<60	D
Below 50	F

Questions

Q.1 Use Nested IF to calculate the Grade of all students according to the given Grade Criteria.

Q.2 Find the Grade of Aarav Sharma.

Q.3 Find the Grade of Riya Kapoor.

Q.4 Find the Grade of Karan Mehta.

Q.5 Find the Grade of Neha Singh.

Q.6 Find the Grade of Simran Kaur.

Q.7 Find the Grade of Mohit Jain.

Q.8 Find the Grade of Pooja Gupta.

Q.9 Find the Grade of Rahul Saini.

Q.10 Find the Grade of Ananya Sharma and calculate how many students received A+ Grade.

Formula Task

Assume:

• A2 = Student Name

• B2 = Total Marks

• C2 = Grade

Enter the following Nested IF formula in C2:

=IF(B2>=90,"A+",IF(B2>=85,"A",IF(B2>=80,"B+",IF(B2>=75,"B",IF(B2>=70,"C+",IF(B2>=65,"C",IF(B2>=60,"D+",IF(B2>=50,"D","F"))))))))

Then drag the formula down from C2 to C11.

Count A+ Students

After calculating all grades, use:

=COUNTIF(C2:C11,"A+")

This will calculate the total number of students who received A+.

Important Point

The conditions should be checked from highest marks to lowest marks:

90 → 85 → 80 → 75 → 70 → 65 → 60 → 50 → Below 50

For example:

• 96 → A+

• 89 → A

• 83 → B+

• 77 → B

• 72 → C+

• 68 → C

• 63 → D+

• 57 → D

• 48 → F

Skills Covered

Students will learn:

• Nested IF

• Multiple conditions

• Grade calculation

• Logical decision-making

• Using COUNTIF with calculated results

• Copying formulas using Fill Handle