More than two answers: IF inside IF

1 / 6

Three possible answers: Merit for 70 or more, Pass for 50 or more, otherwise Fail.

keyboard Typing formulas is easiest on a computer with a keyboard.

A1
ABCD
1NameScoreResult
2Tariq76
3Nadia79
4Daniel64
5Quinn98
6Jamal70
7Isla30
8Kofi50
9
10
11
12
scores
Merit, Pass or Fail
Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.
Study notes

How IF and IFS works

  • How do you get three answers from IF?

    One IF picks between two answers. For three, put a second IF where the "if FALSE" answer goes:

    =IF(B2>=70,"Merit",IF(B2>=50,"Pass","Fail"))

  • Why does the order matter?

    Excel checks the questions in order and stops at the first one that is TRUE. So check the highest band first: a score of 85 is also at least 50, and if B2>=50 came first, it would never get as far as "Merit".

  • What is IFS?

    Newer Excel also has IFS, which lists the questions and answers in pairs without the brackets piling up. TRUE as the last question means "anything else":

    =IFS(B2>=70,"Merit",B2>=50,"Pass",TRUE,"Fail")

Know someone fighting with a spreadsheet? Send them this page.