Nested IF for grading
IntermediateIn reporting and analysis, you’ll often need to convert a number into a category (like turning a test score into a letter grade). A nested IF lets you test multiple thresholds in order and return a different result for each case.
In this sheet, column B contains exam scores. The grading scale is listed in columns D and E.
Your task:
- In cell C2, write a nested IF that returns the correct letter grade (A–F) for the score in B2.
- Use the minimum scores in E2:E6 and the corresponding letters in D2:D6.
- Fill the formula down through C9 to grade every student.
Tip: Always check the highest threshold first, then work your way down.
Need some help?
Hint 1
Start your nested IF by checking whether the score meets the highest minimum (the A cutoff), then move down to B, C, and D.
Hint 2
Lock the grading scale references with $ (for example, $E$2 and $D$2) so the cutoffs don’t shift when you fill down.
Hint 3
Your function will end up looking something like =IF(B2>=$E$2, "A", IF(...)) with a few more IFs inside of IFS.
Answer
Exercise
Nested IF for grading
IntermediateIn reporting and analysis, you’ll often need to convert a number into a category (like turning a test score into a letter grade). A nested IF lets you test multiple thresholds in order and return a different result for each case.
In this sheet, column B contains exam scores. The grading scale is listed in columns D and E.
Your task:
- In cell C2, write a nested IF that returns the correct letter grade (A–F) for the score in B2.
- Use the minimum scores in E2:E6 and the corresponding letters in D2:D6.
- Fill the formula down through C9 to grade every student.
Tip: Always check the highest threshold first, then work your way down.
Need some help?
Hint 1
Start your nested IF by checking whether the score meets the highest minimum (the A cutoff), then move down to B, C, and D.
Hint 2
Lock the grading scale references with $ (for example, $E$2 and $D$2) so the cutoffs don’t shift when you fill down.
Hint 3
Your function will end up looking something like =IF(B2>=$E$2, "A", IF(...)) with a few more IFs inside of IFS.