FormulaGym
XP 0
Streak 0

The IF function in Excel

IF shows one result when a test is true and another when it is false.

What IF does

The test is a comparison such as B2>=60. Comparisons use equals, not equal (<>), greater than, less than, and greater or less than or equal to. Text results go in quotes. Copy the formula down a column and the cell reference moves to each row in turn.

Syntax

=IF(logical_test, value_if_true, [value_if_false])
logical_test
A comparison that is either true or false, such as B2>=60.
value_if_true
What to show when the test is true.
value_if_false
Optional. What to show when the test is false. If left out, Excel shows FALSE.

Worked example

ABC
1StudentScoreResult
2Ana72Pass
3Ben48Fail
4Cy64Pass
5Di91Pass
6Eli55Fail
C2 =IF(B2>=60,"Pass","Fail") (copied down 5 rows)

The formula is written once in C2 and copied down. B2 becomes B3, B4 and so on.

Now you try

A fresh exercise every time. Press Another exercise as often as you like.

Where you will use it

Finance

Flag invoices that are over budget.

HR

Mark who has met a target.

Sales & Ops

Label orders as Late or On time.

Common mistakes

  • Forgetting the quotes around text results. Pass must be "Pass".
  • The wrong comparison. >= includes the boundary value and > does not.
  • Typing the cell reference as a fixed address in every row instead of copying the formula down.

The IF course

  1. 1
    Flag each rowFree practice below
    Practise
  2. 2
    Nested IFs for gradesComing soon
  3. 3
    IF with AND and ORComing soon
  4. 4
    Handle blanks safelyComing soon
  5. 5
    Commission tiersComing soon
  6. 6
    Replace errors with IFERRORComing soon

Common questions

Can IF return a number or a calculation?

Yes. The true and false results can be text, numbers, cell references or other formulas.

What happens if I leave out the false result?

Excel returns FALSE when the test fails.

Related functions