Logic › IF42 / 144

Progress is saved on this device. Sign in or sign up free to keep it in your account.

MediumFreeIF

Nested IF: letter grades A to D

Mr. Rudi, a junior high math teacher in Medan, converts final scores into letter grades: A for 90 and above, B for 80 to 89, C for 70 to 79, and D below 70.

Task

Fill C2:C8 with the grade A, B, C, or D using nested IF (an IF inside an IF), without IFS.

Cells to fillC2:C8

Required functionsIF

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Fill downCtrlD
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
Test the highest threshold first. If the score is >=90, it is an A; if not, move on to the next threshold.
Hint 2
The third argument of IF (value if false) can hold another IF. Four grades need only three IFs.
Hint 3
Pattern: =IF(B2>=90,"A",IF(B2>=80,"B",IF(…))).

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

C2:C8

=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","D")))

Write it in C2, then fill down to C8.

Explanation

Excel evaluates IF from the outside in and stops at the first condition that is TRUE. That is why the order of thresholds matters: if B2>=70 were checked first, even a 95 would get a C. You do not need to spell out "80 to 89", because scores of 90 and above are already caught by the first IF. The number of closing brackets at the end equals the number of IFs. Tip: for many levels, IFS or a threshold table with XLOOKUP is easier to read and maintain.

Starting worksheet: Sheet1
Row	A	B	C
1	Student	Final score	Grade
2	Alif	95
3	Bunga	80
4	Cahya	79
5	Damar	70
6	Elsa	64
7	Farhan	89
8	Gita	90
9
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result