- Foundation
- About 13 min
- 5 practice questions
Comparison operators and the IF function
Use the six comparison operators and write IF formulas that return text or numbers, including a simple two-level IF.
If something looks wrong, please tell us.
So far the formulas you wrote always did the same calculation. The IF function lets a spreadsheet decide: show Pass for one result and Fail for another, or give a bonus only when a target is reached. This is the first step from calculating to thinking.
Comparison operators
| Operator | Meaning | Example | Result when A1 = 40 |
|---|---|---|---|
| = | Equal to | =A1=40 | TRUE |
| <> | Not equal to | =A1<>40 | FALSE |
| > | Greater than | =A1>40 | FALSE |
| < | Less than | =A1<40 | FALSE |
| >= | Greater than or equal to | =A1>=40 | TRUE |
| <= | Less than or equal to | =A1<=40 | TRUE |
A comparison gives only TRUE or FALSE. These two answers are called logical values.
The IF function
=IF(test, value_if_true, value_if_false) has three parts separated by commas. The spreadsheet works out the test. If it is TRUE, it returns the second part; if it is FALSE, it returns the third. Text must be placed in double quotation marks, while numbers and cell addresses are not.
A nested IF puts a second IF in the place of the third part, so that three or more outcomes are possible. Read it from the outside in: the first test is tried first, and the inner IF is used only if the first test is FALSE.
Worked examples
Example 1. A1 holds 46. =IF(A1>=40,"Pass","Fail") tests 46 >= 40, which is TRUE, so the cell shows Pass. If A1 held 39, the test is FALSE and the cell shows Fail.
Example 2. Sales are in B2 = 12000. A bonus of 5% is given only when sales are at least 10000: =IF(B2>=10000,B2*5%,0). The test is TRUE, so the result is 12000 x 5% = 600. For sales of 9000 the result would be 0.
Example 3. Attendance is 72 and the rule is: 75 or more is Allowed, otherwise Short. =IF(A1>=75,"Allowed","Short") tests 72 >= 75, which is FALSE, so the answer is Short.
Example 4. Grades with a nested IF: =IF(A1>=80,"A",IF(A1>=60,"B","C")). For A1 = 65 the first test 65 >= 80 is FALSE, so the inner IF runs: 65 >= 60 is TRUE, so the grade is B. For 80 the answer is A and for 59 it is C.
Example 5. Cell A1 holds the word Yes. =IF(A1="Yes",1,0) gives 1. If A1 held No, it would give 0. Text comparisons ignore capital or small letters in most spreadsheets.
Joining tests with AND and OR
AND(test1, test2) is TRUE only when both tests are TRUE. OR(test1, test2) is TRUE when at least one test is TRUE. They are placed inside the first part of an IF. For example, =IF(AND(A1>=40,B1>=40),"Pass","Fail") passes a student only if both subjects reach 40.
Common mistakes
- Forgetting the quotation marks around text results.
- Writing a comma instead of an operator, or =< instead of <=.
- Putting the tests in the wrong order in a nested IF: always start with the most demanding test.
Example 6. A1 = 45 and B1 = 38. AND(45>=40, 38>=40) is FALSE because the second test fails, so the IF returns Fail. With OR in place of AND the answer would be TRUE for the first test alone, so the result would be Pass.
IF asks a question: true gives the first answer, false gives the second.
Key takeaways. Comparisons return TRUE or FALSE. IF chooses between two results, and a nested IF chooses among more. Put text in quotation marks and read nested IFs from the outside in.
Check your understanding
-
1. A1 holds 40. What does =A1>=40 return?
- FALSE
- 40
- TRUE
- Pass
Show answer
Answer: C. 40 is greater than or equal to 40, so the comparison returns TRUE.
-
2. A1 holds 39. What does =IF(A1>=40,"Pass","Fail") show?
- Fail
- Pass
- TRUE
- 39
Show answer
Answer: A. 39 >= 40 is FALSE, so the third part, Fail, is returned.
-
3. Which operator means not equal to?
- !=
- ><
- =/
- <>
Show answer
Answer: D. The symbol <> is the spreadsheet's not-equal operator.
-
4. =IF(A1>=80,"A",IF(A1>=60,"B","C")). What is shown for A1 = 65?
- A
- C
- B
- D
Show answer
Answer: C. 65 >= 80 is FALSE, so the inner IF is used; 65 >= 60 is TRUE, so B is returned.
-
5. Sales are 9000. What does =IF(B2>=10000,B2*5%,0) return?
- 0
- 450
- 500
- 9000
Show answer
Answer: A. 9000 is below 10000, so the test is FALSE and the third part, 0, is returned.
Sign in to keep track of the lessons you finish.