← All lessons
Spreadsheets · Lesson 4 of 10

IF and comparisons

A comparison gives TRUE or FALSE. The symbols are = (equal), <> (not equal), <, >, <= (at most) and >= (at least). =B2>=50 shows TRUE when B2 is 50 or more.

IF turns that into a choice: =IF(test, value if true, value if false). So =IF(B2>=50,"Pass","Fail") shows Pass or Fail. Words go in double quotes; numbers and cell addresses don't.

The two results can be formulas too: =IF(B2>100,B2*0.9,B2) takes 10% off orders over 100 and leaves the rest alone.

Read the wording carefully: "over 100" is >100, but "100 or more" is >=100. That one character is the most common IF mistake.

Practice

50 or more is a pass. Show "Pass" or "Fail" in C2; it's copied down to C5.

ABC
1StudentScoreResult
2Ana78
3Ben44
4Cara91
5Dev50

C2

More info

Quick reference

= <> < > <= >=Equal, not equal, less, more, at most, at least.
IF(test, if true, if false)One of two results.
"text"Words in a formula go in double quotes.

The practice answers

Other formulas that give the same results count too.

  1. 50 or more is a pass. Show "Pass" or "Fail" in C2; it's copied down to C5. =IF(B2>=50,"Pass","Fail")
  2. Is the stock below its reorder level? Show TRUE or FALSE in D2, copied down to D4. =B2<C2
  3. Orders over 100 get 10% off. In C2, show what each customer pays (copied down to C5). =IF(B2>100,B2*0.9,B2)
  4. Shipping is free from 50 up, and 4.99 below that. Work it out in B3. =IF(B2>=50,0,4.99)

An unofficial course. Not made by or affiliated with Microsoft; Excel is a trademark of Microsoft Corporation.