Several outcomes: IF inside IF

A test gets one of three grades: from 85 points "Excellent", from 60 "Pass", and below 60 "Retake". But IF has only two answers, one for "yes" and one for "no". The formula in C2 knows only pass and retake:

Student

Score

Grade

Emma

Ben

Olivia

Leo

Mia

Jack

Sheet1

A second IF in place of "otherwise"

The third argument of IF says what to show when the condition is false. It can hold not just a word but a whole formula, including another IF. You don't put = before the second IF: it's only needed at the start of a formula. This turns two outcomes into three:


=IF(B2>=85,"Excellent",IF(B2>=60,"Pass","Retake"))

It reads like this: if the score is 85 or more, then "Excellent", otherwise if it's 60 or more, then "Pass", otherwise "Retake".

Excel goes through the checks in turn and stops at the first true one. It gets to the second IF only when the first condition is false. Pick a student and see which check Excel stops at:

=IF(B2>=85,"Excellent",IF(B2>=60,"Pass","Retake"))

First IF92 >= 85?TRUE

"Excellent"

Second IF92 >= 60?not checked

"Pass"

Otherwiseboth checks FALSE

"Retake"

For Emma, the first condition is true, so the cell gets "Excellent". Excel never gets to the second IF.

The order of checks

Since Excel takes the first true check, you start with the highest threshold. If you start with B2>=60, Emma with 92 points gets "Pass": that condition is true for her too, and the check for "Excellent" is never reached.

If the thresholds are checked with "less than", it's the other way around: the smallest one goes first. For example, B2<10 goes before B2<30, otherwise the number 5 already stops at B2<30.

Parentheses

Each IF has its own pair of parentheses, and in the diagram above they share a color. The second IF sits entirely inside the first, so the formula ends with two parentheses in a row: the first one closes the second IF, and the second one closes the first.

There are as many closing parentheses at the end as there are IFs in the formula. If Excel refuses to accept a formula, count the parentheses first: there should be as many opening ones as closing ones.

Testing the boundaries

Mistakes most often hide right at the thresholds. By the rule "from 85 points", exactly 85 already gives "Excellent", so the formula uses the >= sign. With the > sign, Ben with 85 points would get "Pass".

A finished formula is tested on numbers exactly at the threshold and one below it: 85 and 84, 60 and 59. The table below already has such students: Ben, Leo, Olivia and Mia. Rewrite the formula in C2 for three outcomes:

Rewrite C2: first the check for 85 points and "Excellent", and put the current IF in place of "otherwise"