Free demo · Microsoft Excel · Lesson 6 · Intermediate · Logical functions

The IF function: let Excel decide

IF asks a yes-or-no question about a cell, then shows one answer when it is true and another when it is false.

fx=IF(logical_test, value_if_true, value_if_false)
Course | Children | 2nd trainer? | Trainers Art | 24 | =IF(B2>=20,"Yes","No")→Yes | =IF(B2>=20,2,1)→2 Sport | 31 | =IF(B3>=20,"Yes","No")→Yes | =IF(B3>=20,2,1)→2 Computers | 18 | =IF(B4>=20,"Yes","No")→No | =IF(B4>=20,2,1)→1 Music | 15 | =IF(B5>=20,"Yes","No")→No | =IF(B5>=20,2,1)→1
  1. 1In C2 type =IF(B2>=20,"Yes","No"): the test, then the two answers
  2. 2Press Enter: 24 ≥ 20 is TRUE, so Yes
  3. 3Fill down: each row tests its own number. 18 and 15 give No
  4. 4Text goes in quotes, numbers don't: in D2 type =IF(B2>=20,2,1)
  5. 5Enter, then fill down: 2, 2, 1, 1, numbers you can add up
  6. ✓Computers grows to 20: the answers flip to Yes and 2 (>= includes 20)
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. Enrol

Tip: press → or Space to step through, ← to go back, R to replay.

About this lesson

IF is the first logical function most people learn in Excel, because it lets a worksheet make a decision. Its three arguments are =IF(logical_test, value_if_true, value_if_false). The logical test is a comparison that is either TRUE or FALSE, written with an operator such as =, >, <, >= (greater than or equal to) or <> (not equal to). In this lesson =IF(B2>=20,"Yes","No") marks every summer camp course that has 20 children or more, and =IF(B2>=20,2,1) returns the number of trainers it needs. Text answers are typed in double quotes; numbers and cell references are not, so the result of an IF can be used in further calculations. Filled down the column, the formula tests each row's own value, and the answer changes by itself when the number changes. The full course goes on to IF with calculations, nested IF and IFS for grades, combining tests with AND, OR and NOT, and catching errors with IFERROR.

Key points

  • The three arguments of IF: test, value if true, value if false
  • Text answers in quotes, numbers without
  • Fill down: every row tests its own cell