ASSIGNMENT – 30 — ANSWER KEY
Use of Formula – NESTED IF
Q.1 — Calculate Grade of All Students
Nested IF Formula
Assuming:
-
Column A = NAME
-
Column B = TOTAL MARKS
-
Column C = GRADE
Enter 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 to C11.
Completed Result
| NAME | TOTAL MARKS | GRADE |
|---|---|---|
| Aarav Sharma | 96 | A+ |
| Riya Kapoor | 89 | A |
| Karan Mehta | 83 | B+ |
| Neha Singh | 77 | B |
| Rohit Verma | 72 | C+ |
| Simran Kaur | 68 | C |
| Mohit Jain | 63 | D+ |
| Pooja Gupta | 57 | D |
| Rahul Saini | 48 | F |
| Ananya Sharma | 92 | A+ |
Q.2 — Grade of Aarav Sharma
Marks = 96
Answer: A+
Q.3 — Grade of Riya Kapoor
Marks = 89
Answer: A
Q.4 — Grade of Karan Mehta
Marks = 83
Answer: B+
Q.5 — Grade of Neha Singh
Marks = 77
Answer: B
Q.6 — Grade of Simran Kaur
Marks = 68
Answer: C
Q.7 — Grade of Mohit Jain
Marks = 63
Answer: D+
Q.8 — Grade of Pooja Gupta
Marks = 57
Answer: D
Q.9 — Grade of Rahul Saini
Marks = 48
Answer: F
Q.10 — Grade of Ananya Sharma & A+ Count
Ananya Sharma
Marks = 92
Answer: A+
Count of A+ Students
Use:
=COUNTIF(C2:C11,"A+")Answer:
2 students
Students who received A+:
-
Aarav Sharma – 96
-
Ananya Sharma – 92
Final Answer Summary
| Q. | Student | Marks | Grade |
|---|---|---|---|
| Q.2 | Aarav Sharma | 96 | A+ |
| Q.3 | Riya Kapoor | 89 | A |
| Q.4 | Karan Mehta | 83 | B+ |
| Q.5 | Neha Singh | 77 | B |
| Q.6 | Simran Kaur | 68 | C |
| Q.7 | Mohit Jain | 63 | D+ |
| Q.8 | Pooja Gupta | 57 | D |
| Q.9 | Rahul Saini | 48 | F |
| Q.10 | Ananya Sharma | 92 | A+ |
⭐ Final Result
A+ = 2 students
A = 1 student
B+ = 1 student
B = 1 student
C+ = 1 student
C = 1 student
D+ = 1 student
D = 1 student
F = 1 student
Total Students = 10