← VCE Spreadsheets, SQL & Visualisation

The IF function in a spreadsheet

Checks a test, like B2>=50. Shows the second value if the test is true and the third if it is false. Text results go in quotes, e.g. =IF(B2>=50,"Pass","Fail").

Part of: VCE Applied Computing - U1O1 - Data analysis — Module 16, VCE Data Analytics - U3O1 - Data Analytics — Module 69, VCE Data Analytics - U4O1 - Development and Evaluation — Module 10, 7-8 Digital Technologies: Analysing and visualising data — Module 5, ACARA Digital Technologies Years 9–10 — Acquiring and Validating Data — Module 76

Understanding The IF function in a spreadsheet in VCE Spreadsheets, SQL & Visualisation

What it does: Going and Away are text results, so they are in quotes. The text being compared with, "Yes", is in quotes too. When =IF(B2>=50,"Pass","Fail") is copied down one row, the cell reference updates to B3. Each row is then tested using its own mark. 4 is not greater than 10, so the test is false. IF shows the third part, "Small". This formula has no value for when the test is false. To give a result for both cases, include a test, a true value and a false value.

Common mistakes: Text results must be in quotes. Pass should be "Pass", otherwise the spreadsheet thinks it is a name and shows an error. When the formula is copied down, the row number changes. This row should check B3, not B2, so it matches its own row.

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 4–20) and word search (Modules 4–34).

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" if it is not?
=IF(B2>=50,"Fail","Pass")=IF(B2>=50,"Pass","Fail")
Matches
=IF(B2>=50,Pass,Fail)
↓
The text results Pass and Fail are missing quotes. They should be "Pass" and "Fail".
Applying
Meanings
Term
=IF(test, value_if_true, value_if_false)
Meaning
Checks a test, like B2>=50. Shows the second value if the test is true and the third if it is false. Text results go in quotes, e.g. =IF(B2>=50,"Pass","Fail").
Practice
=IF(B2>=50,"Pass","Fail")
Testing
Gaps
=(B2>=50,"Pass","Fail")
Check Answer
Spots
1=IF(B2>=50,Pass,"Fail")
2=IF(B3>=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" if it is not?
A. =IF(B2>=50,"Fail","Pass")
B. =IF(B2>=50,"Pass","Fail") ✓
The second part of IF is the result when the test is TRUE. B2>=50 is true for a pass, so "Pass" comes second and "Fail" comes third.
2. In =IF(A2>10,"Big","Small"), what shows if A2 is 4?
A. Small ✓
B. Big
4 is not greater than 10, so the test is false. IF shows the third part, "Small".
3. Which one writes the text results correctly?
A. =IF(C2>=100,"Yes","No") ✓
B. =IF(C2>=100,Yes,No)
Text results must go inside quotation marks. Without quotes, the spreadsheet thinks Yes and No are names and shows an error.
4. How many parts does an IF function have inside its brackets?
A. 3: a test, a value if true and a value if false ✓
B. 2: a test and one result
IF needs a test, then the result for true, then the result for false. Each part is separated by a comma.
5. You wrote =IF(B2>=50,"Pass","Fail") in C2. You copy it down to C3. What does the formula in C3 test?
A. B2>=50
B. B3>=50 ✓
When you copy a formula down, the row number changes. C3 tests the mark next to it in B3, so every row is checked.

Match the Pairs

=IF(B2>=50,"Pass","Fail") → Shows Pass if the mark in B2 is 50 or more, otherwise shows Fail
=IF(C2>100,"Over","OK") → Shows Over if C2 is bigger than 100, otherwise shows OK
Text results must go inside quotes so the spreadsheet reads them as words → =IF(A2="Yes","Going","Away")
Copying the formula down from row 2 changes B2 to B3, B4 and so on, so every row gets its own result → =IF(B3>=50,"Pass","Fail")
=IF(B2>=50,Pass,Fail) → The text results Pass and Fail are missing quotes. They should be "Pass" and "Fail".
=IF(B2>=50,"Pass") → The formula has no third value for when the test is false. It needs a value_if_false such as "Fail".

Key Term

=IF(test, value_if_true, value_if_false)
Checks a test, like B2>=50. Shows the second value if the test is true and the third if it is false. Text results go in quotes, e.g. =IF(B2>=50,"Pass","Fail").

Fill in the Blanks

1. =IF(B2>=50,"Pass","Fail")
Hint: Type the function name that picks one of two results based on a test.
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 symbol that every spreadsheet formula must start with.
4. C2: =IF(B2>=50,"Pass","Fail") C3: =IF(B3>=50,"Pass","Fail")
Hint: The formula was copied down one row. Type the cell reference that now holds the mark being tested.
5. =IF(B2<50,"Fail","Pass")
Hint: Type the operator that tests whether the mark is less than the pass mark.

Spot the Error

Question 1
A =IF(B2>=50,Pass,"Fail")
B =IF(B3>=50,"Pass","Fail")
C =IF(B4>=50,"Pass","Fail")
Line A — Text results must be in quotes. Pass should be "Pass", otherwise the spreadsheet thinks it is a name and shows an error.
Question 2
A =IF(B2>=50,"Pass","Fail")
B =IF(B2>=50,"Pass","Fail")
C =IF(B4>=50,"Pass","Fail")
Line B — When the formula is copied down, the row number changes. This row should check B3, not B2, so it matches its own row.
Question 3
A =IF(B2>=50,"Pass","Fail")
B =IF(B3=>50,"Pass","Fail")
C =IF(B4>=50,"Pass","Fail")
Line B — The comparison operator is written the wrong way round. 'Greater than or equal to' is >=, not =>.
Question 4
A =IF(C2>10,"Yes","No")
B =IF(C3>10,"Yes","No")
C =IF(C4>10,"Yes","No")
D IF(C5>10,"Yes","No")
Line D — A formula must start with an equals sign. It should be =IF(C5>10,"Yes","No"), or the cell just shows the text.
Question 5
A =IF(B2>=30,"Hot","Mild")
B =IF(B3>=30,"Hot","Mild")
C =IF(B4>=30,"Hot","Mild")
D =IF(B5>=30,"Mild","Hot")
Line D — The results are in the wrong order. The second value shows when the test is true, so a temperature of 30 or more should give "Hot".

Student with a class code? Join your class →