In this formula, the following arguments are used:
ifs-expression: B2=4, which checks to see if B2 is equal to 4.
ifs-true: "Distinguished", the string that is returned if B2 is equal to 4.
ifs-expression…: "B2=3", which checks to see if B2 is equal to 3.
ifs-true…: "Proficient", the string that is returned if B2 is equal to 3.
ifs-expression…: "B2=2," which checks to see if B2 is equal to 2.
ifs-true…: "Apprentice", the string that is returned if B2 is equal to 2.
ifs-expression…: "B2=1", which checks to see if B2 is equal to 1.
ifs-true…: "Novice", the string that is returned if B2 is equal to 1.
To use the formula above with the other cells in the table, change B2 in each if-expression to another cell containing a score.
Using TRUE for ifs-expression…
Sometimes, none of the expressions evaluate to TRUE, but instead of returning an error, you can set the returned string in the final ifs-expression. In the table above, Student 4 does not have a score, so the previous formula would return an error for that student. Maybe this student was absent and still needs to complete the assignment, so rather than leave the error, you can add another ifs-expression to mark missing scores as "Incomplete":
To determine whether two values aren’t equal, use the comparison operator <>.
Additional examples
=IFS(A2>91,"A",A2>82,"B",A2>73,"C",A2>64,"D",TRUE,"F") returns the letter grade "A" for a number greater than 91, then returns a "B" for a number greater than 82 but less than 92, and so on for all values less than 65, which returns an "F".
=IFS(A2>91,"A",A2>82,"B",A2>73,"C",A2>64,"D",A2<>0,"Attempted",TRUE,"Failed") returns the letter grade "A" for a number greater than 91, then returns a "B" for a number greater than 82 but less than 92, and so on for all values less than 65 but not equal to 0, which returns an "Attempted". If the score is 0, the formula returns "Failed".
Let A2 contain "A dog"
Let A1 = COUNTMATCHES(A2, REGEX("\w+"))
=IFS(A1 = 0, "No word", A1 = 1, "One word", A1 = 2, "Two words", A1 > 2, "Multiple words") returns "Two words".