Sample Table
Nam Scor Grad Subje Remarks
e e e ct
Anna 85 B Math Passed
Ben 92 A Englis Passed
h
Carla 76 C Scienc Passed
e
Davi 58 F Math Failed
d
Ella 67 D Englis Condition
h al
🧠 Quiz: Write the Correct Excel Formula
Instructions: Based on the table above, write the correct formula using the
specified function.
1. Write a formula to check if Anna's score is greater than 80. If yes,
return "High", else "Low".
2. Write a formula to return "Passed" if Carla's score is 75 or above,
otherwise "Failed".
3. Write a formula to check if Ella's remarks are "Conditional". If yes,
return "Needs Review", else "OK".
4. Write a formula to assign a grade based on score:
o ≥90 → "A"
o ≥80 → "B"
o ≥70 → "C"
o ≥60 → "D"
o <60 → "F"
5. Write a formula to categorize remarks:
o "Passed" → "Clear"
o "Failed" → "Retake"
o "Conditional" → "Pending"
6. Write a formula to classify subjects:
o "Math" → "STEM"
o "Science" → "STEM"
o "English" → "Arts"
7. Write a formula to convert grade to description:
o "A" → "Excellent"
o "B" → "Good"
o "C" → "Average"
o "D" → "Below Average"
o "F" → "Fail"
8. Write a formula to convert remarks to numeric codes:
o "Passed" → 1
o "Conditional" → 2
o "Failed" → 0
9. Write a formula to convert subject to department:
o "Math" → "Mathematics Dept"
o "English" → "Language Dept"
o "Science" → "Science Dept"
10. Write a formula to sum all scores for students who passed.
11. Write a formula to sum scores for students in the "Math" subject.
12. Write a formula to sum scores for students with grade "A".
13. Write a formula to count how many students got a grade of "B".
14. Write a formula to count how many students have "Passed" as
remarks.
15. Write a formula to count how many students scored below 70.
