← Years 9–10 Data & Information

The IF function in a spreadsheet

Checks a condition, like B2>=50, and shows one result if it is true and another if it is false. Text results go in quotes, and you can copy it down a column.

Part of: 9-10 Digital Technologies: Acquiring, storing and validating data — Module 12, ACARA Digital Technologies Years 7–8 — Acquiring and Storing Data — Module 51, ACARA Digital Technologies Years 7–8 — Analysing and Visualising Data — Module 28, ACARA Digital Technologies Years 7–8 — Digital Footprints and Data Minimisation — Module 44

Understanding The IF function in a spreadsheet in Years 9–10 Data & Information

What it does: "Pass" and "Fail" are in quotes, so the cell displays those words. Without the quotes the spreadsheet would look for named ranges and give an error. When =IF(B2>=50,"Pass","Fail") is copied down one row, the relative reference B2 changes to B3, so each row tests its own mark. The test B2>=50 uses 'greater than or equal to'. 50 is equal to 50, so the test is true and the formula shows the first result, Pass. The first part of IF is the condition that gets checked. It is either true or false. The next two parts are the results.

Common mistakes: Text results must be inside quotes. Without quotes, Excel thinks Pass is a name and shows an error. It should be "Pass". When the formula is copied down to row 3, the test should check B3. Here it still checks B2, so row 3 would show the wrong result. It should be B3>=50.

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add The IF function in a spreadsheet to a class →

This module's key terms are also in a printable crossword (Modules 17–51) and word search (Modules 6–132).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
Which formula shows "Pass" when the mark in B2 is 50 or more, and "Fail" otherwise?
=IF(B2>=50,"Pass","Fail")=IF(B2>=50,Pass,Fail)
Matches
=IF(B2>=50,Pass,Fail)
↓
The text results Pass and Fail are missing quotation marks. They need to be written as "Pass" and "Fail".
Applying
Meanings
Term
=IF(test, value_if_true, value_if_false)
Meaning
Checks a condition, like B2>=50, and shows one result if it is true and another if it is false. Text results go in quotes, and you can copy it down a column.
Practice
=IF(B2>=50,"Pass","Fail")
Testing
Gaps
=(B2>=50,"Pass","Fail")
Check Answer
Spots
1C1: Result
2C2: =IF(B2>=50,Pass,"Fail")

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Woodpecker' badge — Tap Tap Tap, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. Which formula shows "Pass" when the mark in B2 is 50 or more, and "Fail" otherwise?
A. =IF(B2>=50,"Pass","Fail") ✓
B. =IF(B2>=50,Pass,Fail)
Text results must be inside quotes. Without quotes, the spreadsheet thinks Pass and Fail are names and shows an error.
2. What does =IF(B2>=50,"Pass","Fail") show when B2 contains 50?
A. Fail
B. Pass ✓
The test B2>=50 uses 'greater than or equal to'. 50 is equal to 50, so the test is true and the formula shows the first result, Pass.
3. In =IF(B2>=50,"Pass","Fail"), which part is the test?
A. "Pass"
B. B2>=50 ✓
The first part of IF is the condition that gets checked. It is either true or false. The next two parts are the results.
4. What does =IF(B2>=50,"Pass","Fail") show when B2 contains 49?
A. Fail ✓
B. Pass
49 is less than 50, so the test is false. IF then shows the third part, the value_if_false.
5. You typed =IF(B2>=50,"Pass","Fail") in C2. What happens when you copy it down to C3?
A. It becomes =IF(B3>=50,"Pass","Fail") and checks the mark in B3 ✓
B. It stays =IF(B2>=50,"Pass","Fail") and checks B2 again
Cell references are relative by default. When you copy a formula down a column, B2 changes to B3, B4 and so on, so each row checks its own mark.

Match the Pairs

=IF(B2>=50,"Pass","Fail") → Shows Pass if the mark in B2 is 50 or more, otherwise shows Fail
=IF(C2<18,"Minor","Adult") → Shows Minor if the age in C2 is under 18, otherwise shows Adult
Text results must be put inside quotation marks so the spreadsheet shows them as words → =IF(B2>=50,"Pass","Fail")
Copy the formula down a column so each row tests its own cell (B3, B4 and so on) → =IF(B3>=50,"Pass","Fail")
=IF(B2>=50,Pass,Fail) → The text results Pass and Fail are missing quotation marks. They need to be written as "Pass" and "Fail".
=IF(B2>=50,"Pass") → The value_if_false argument is missing. The function needs a test, a true result and a false result, such as "Fail".

Key Term

=IF(test, value_if_true, value_if_false)
Checks a condition, like B2>=50, and shows one result if it is true and another if it is false. Text results go in quotes, and you can copy it down a column.

Fill in the Blanks

1. =IF(B2>=50,"Pass","Fail")
Hint: Type the name of the function that chooses between two results.
2. =IF(B2>=50,"Pass","Fail")
Hint: Type the comparison operator that means greater than or equal to.
3. =IF(B2>=50,"Pass","Fail")
Hint: Type the cell reference that holds the mark being tested.
4. =IF(B3>=50,"Pass","Fail")
Hint: This formula was copied down from row 2 to row 3. Type the cell reference it now tests.
5. =IF(B2<50,"Fail","Pass")
Hint: Type the comparison operator that means less than.

Spot the Error

Question 1
A C1: Result
B C2: =IF(B2>=50,Pass,"Fail")
C C3: =IF(B3>=50,"Pass","Fail")
Line B — Text results must be inside quotes. Without quotes, Excel thinks Pass is a name and shows an error. It should be "Pass".
Question 2
A C1: Result
B C2: =IF(B2>=50,"Pass","Fail")
C C3: =IF(B2>=50,"Pass","Fail")
Line C — When the formula is copied down to row 3, the test should check B3. Here it still checks B2, so row 3 would show the wrong result. It should be B3>=50.
Question 3
A B2: 62
B C2: IF(B2>=50,"Pass","Fail")
C C2 shows Pass
Line B — A formula must start with an equals sign. Without it, the cell just shows the text. It should be =IF(B2>=50,"Pass","Fail").
Question 4
A B2: 92
B C2: =IF(B2=>90,"Great","Low")
C C2 shows Great
Line B — The 'greater than or equal to' operator is >=, not =>. The test should be B2>=90.
Question 5
A =IF(B2>=50 "Pass","Fail")
B Copy it down column C
C C3 uses B3 in its test
Line A — Each part of IF must be separated by a comma. A comma is missing after the test B2>=50.

Student with a class code? Join your class →