Skip to content
Tutorials Center
My learning

Topics

Lesson 16 of 93 ยท Logical and Conditional Formulas

IF and Nested IF

Learn how the Excel IF function makes decisions, how comparison operators work, how to nest IF safely, and when IFS is a clearer choice.

Until now your formulas have always done the same thing for every row. The IF function lets a formula choose. It tests a condition and returns one result when the condition is true and another when it is false: "Pass" or "Fail", "Late" or "On time", a 10% discount or none. Almost every real spreadsheet uses IF somewhere, and it is the foundation for the other logical functions in this chapter.

You should already know how to write a formula and how cell references behave when you copy them. This lesson covers the syntax of IF, the comparison operators, how to nest several IF calls, and how to keep nested formulas readable.

A note on honesty: Excel itself cannot run on this page. The formulas below are real Excel formulas, and the live examples run them through a stand-in: a small JavaScript spreadsheet engine that mimics how Excel evaluates them. It is not Excel's code, but for these examples it gives the results Excel gives.

IF has three parts

Test A condition that is TRUE or FALSE.
If true The result when the test passes.
If false The result when it fails.

=IF(test, value_if_true, value_if_false)

1 / 2

The syntax of IF

TEXT
=IF(logical_test, value_if_true, [value_if_false])
  • logical_test is any expression that evaluates to TRUE or FALSE, such as B2>=50.
  • value_if_true is what the cell shows when the test is true.
  • value_if_false is optional. If you leave it out (and keep no comma), a false test returns the logical value FALSE.

The results can be numbers, text in double quotes, other formulas, or cell references:

TEXT
=IF(B2>=50, "Pass", "Fail")
=IF(C2>1000, C2*0.9, C2)
=IF(D2="", "Missing", D2)

Text results must be in double quotes. Without the quotes Excel looks for a name called Pass and shows #NAME?.

Only the branch that is chosen is calculated. That matters when the other branch would cause an error, for example a division by zero: =IF(B2=0, 0, A2/B2) is safe because the division never runs when B2 is zero.

Comparison operators

The test uses one of six comparison operators:

Operator Meaning Example
= equal to A1="Paid"
<> not equal to A1<>0
> greater than A1>100
>= greater than or equal to A1>=100
< less than A1<0
<= less than or equal to A1<=0

Two details trip beginners. First, text comparison ignores upper and lower case: "paid"="PAID" is TRUE. Second, comparing different types follows a fixed order: numbers are smaller than text, and text is smaller than logical values. So a number stored as text can behave in surprising ways, which you will see below.

A first IF in the stand-in engine

JS IF with a pass mark

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Chen scored exactly 50, and >= counts that as a pass. Dara scored 0, yet column D shows "no score" instead of #DIV/0!, because the division is never evaluated when the test is false.

Nesting IF

When two outcomes are not enough, put another IF in the value_if_false position. Each level handles one more case:

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

Excel reads this left to right. It checks B2>=90 first; only when that fails does it check B2>=80, and so on. The last argument is the "everything else" result. Current versions of Excel allow up to 64 levels of nesting, but you should stop long before that: each level makes the formula harder to read and to change.

flowchart TD
  A["B2 >= 90?"] -->|yes| GA["A"]
  A -->|no| B["B2 >= 80?"]
  B -->|yes| GB["B"]
  B -->|no| C["B2 >= 70?"]
  C -->|yes| GC["C"]
  C -->|no| GD["D"]

Nested IF is a chain of questions; the first "yes" wins.

Order matters

Because the first true test wins, the order of the tests decides the result. If you test the low limit first, every high score is caught by it:

JS Nested IF: right order, wrong order, and IFS

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The "wrong" column returns C for 95 and 85, because >=70 is true for them and Excel never looks further. The last column shows that IFS has no built-in "else": when no test is true you get #N/A. The usual fix is a final pair whose test is simply TRUE.

IFS: a flatter alternative

IFS (available in Excel 2019 and Microsoft 365) takes pairs of test and result and returns the result of the first true test. There is no nesting and no closing-parenthesis pile-up:

TEXT
=IFS(B2>=90,"A", B2>=80,"B", B2>=70,"C", TRUE,"D")

Older versions such as 2016 do not have IFS; nested IF is the compatible choice there. The next lessons cover AND, OR and NOT for combining tests, and IFS, SWITCH and CHOOSE in more depth.

Keeping formulas readable

  • Do not hardcode limits. Put 90, 80 and 70 in cells (say F2:F4) and refer to them with $F$2. Then a policy change edits one cell, not a hundred formulas.
  • Use line breaks. In the formula bar, Alt+Enter inserts a line break inside a formula, so each level can sit on its own line. Excel ignores the extra whitespace.
  • Switch to a lookup table once you pass three or four levels. An approximate-match lookup (see HLOOKUP and approximate-match lookups) replaces a long chain.
  • Check the edges. Test values exactly at each limit (89, 90, 91) to be sure > versus >= does what you intend.

Type surprises in tests

The comparison rules above mean a text value that looks like a number is not treated as one:

JS Comparisons that surprise beginners

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Text compares as larger than any number, so "10">5 is TRUE but for the wrong reason, and a text "10" never equals the number 10. If a test fails mysteriously, check whether your numbers are really numbers; Text, VALUE and number-as-text problems shows how to fix that.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does =IF(A1>10, "High") return when A1 is 5?

    When the value_if_false argument is omitted, a false test returns the logical value FALSE.
  2. 2. In a nested IF that gives grades, why must the test for 90 come before the test for 70?

    Excel stops at the first test that is true. If the lowest limit comes first, higher scores match it and never reach the later tests.
  3. 3. What happens with =IFS(A1>5,"Big") when A1 is 2?

    IFS returns #N/A when no test is true. Add a final TRUE test with a default result to avoid it.
  4. 4. Why is =IF(B2=0, 0, A2/B2) safe when B2 is 0?

    IF evaluates only the branch selected by the test, so the division never runs when B2 is 0.

Summary

  • =IF(test, value_if_true, [value_if_false]) returns one of two results; a missing false result gives FALSE.
  • The six comparison operators are =, <>, >, >=, <, <=; text comparison ignores case.
  • Nest IF in the false position for more cases; the first true test wins, so order the tests carefully.
  • IFS (2019 and 365) is flatter, but needs a final TRUE test to act as "else".
  • Keep limits in cells, break long formulas over lines, and move to a lookup table when the chain gets long.

Next: AND, OR, NOT and XOR.