How to use IFS to turn a mark into a letter grade
Marks out of 100 sorted into A to F by a single formula. Each boundary is tested in turn and the first one that fits decides the grade, so a pupil who missed the paper is never given an F.
The formula
=IFS(NOT(ISNUMBER(B2)),"Absent",B2<50,"F",B2<60,"D",B2<70,"C",B2<85,"B",TRUE,"A") in column C
What each part does
NOT(ISNUMBER(B2)), "Absent"- If B on this row holds anything other than a number, the answer is "Absent". Tested first, because text counts as larger than any number in Excel.
B2<50, "F"- If B on this row is < 50, the answer is "F".
B2<60, "D"- If B on this row is < 60, the answer is "D".
B2<70, "C"- If B on this row is < 70, the answer is "C".
B2<85, "B"- If B on this row is < 85, the answer is "B".
TRUE, "A"- What to show when none of the conditions above are met.
How it works, and what to watch for
Anything that is not a number is caught first: a dash, a note, an empty cell. Text counts as larger than every number in Excel, so a mark left as a word would otherwise sail past each boundary and come out as an A.
The boundaries run from the lowest upwards, each one an "under" test. Read in order they match how a mark scheme is usually written, and a boundary can be moved without touching the rest.
IFS needs Excel 2019 or Microsoft 365. Set the version to an earlier one and the same bands come out as a nested IF, which every version understands.
Questions
Why not one IF for each grade in its own column?
It works, but five columns have to be kept in step and the result is spread out. One formula in one column keeps the grade beside the mark.
What happens to a mark exactly on a boundary?
An "under 60" test leaves 60 itself out, so a mark of exactly 60 takes the grade above. Use "at most 60" if the boundary should be included.
Can the labels come from cells instead?
Yes. Put the thresholds and labels in two columns and use LOOKUP against them; changing a band then means editing a cell, not the formula.
The example sheet
| Student |
Mark |
Grade |
| Emma |
78 |
|
| Thomas |
54 |
|
| Lena |
- |
|
| Martin |
91 |
|
If this helped, these are close by
More on IFS, and every walkthrough that uses it.
