Formulas & Math

Spreadsheet Functions

Welcome to Spreadsheet Functions! In this strand, you will master using built-in formulas, mathematical operators, statistical averages, conditional logic (IF), and lookups to transform raw data into powerful automated insights.

🎨Official Curriculum Sketchnote • Synthesis Poster🏆 100% Mastered

SPFN VISUAL SUMMARY

Spreadsheet Functions

Congratulations on mastering Levels 1 to 6! Here is your one-page visual synthesis of every key concept learned:

▶View All 6 Level Key Concepts & Standards6 Levels Summarised

📌

Level 1🚀

AutoSum & Addition

Using AutoSum and simple addition formulas.

Key Takeaway:Instead of adding numbers on paper, spreadsheets do the math for you! By using the SUM formula, the computer adds up entire columns instantly and updates the answer if any number changes.

#formulas#autosum#addition

✓ Mastered

📌

Level 2🛡️

Basic Math Operators

Using standard operators (+, -, *, /) for calculations.

Key Takeaway:Computers use special key symbols for math: asterisk (*) means multiply, and forward slash (/) means divide.

#operators#multiplication#division

✓ Mastered

📌

Level 3🔑

AVERAGE, MAX & MIN

Using statistical summary functions.

Key Takeaway:Scanning hundreds of rows to find the highest or lowest number takes time.

#average#max#min

✓ Mastered

📌

Level 4💻

COUNT & COUNTIF

Counting items conditionally with COUNTIF.

Key Takeaway:Manual counting leads to human mistakes! The =COUNTIF(range, "criteria") function scans your data automatically and counts only the cells that match your exact condition.

#count#countif#criteria

✓ Mastered

📌

Level 5🔐

Logical IF Statements

Automating decisions using IF logic.

Key Takeaway:The =IF(logical_test, value_if_true, value_if_false) statement allows the spreadsheet to think! If a student's score is 50 or higher, it outputs 'Pass'; otherwise, it outputs 'Needs Retake'.

#if-statement#logical-tests#boolean

✓ Mastered

📌

Level 6🏆

VLOOKUP & Lookups

Connecting relational data using VLOOKUP/XLOOKUP.

Key Takeaway:In real-world databases, tables are linked together! VLOOKUP searches a key (like a Student ID or Barcode) in a master table and automatically pulls back the correct name or price into your active sheet.

#vlookup#xlookup#relational-data

✓ Mastered

🧩 TOP SECRET • VAULT KEY SHARD REVEALED✓ 100% STRAND MASTERY REWARD

🗝️

STRAND KEY SHARD #5 OF 8

Area Boss Cipher Fragment Discovered!

Congratulations on completing all 6 levels of Spreadsheet Functions! To unlock the Data Detective Agency, you must collect all 8 Key Shards across the Data, Spreadsheets & Science strands. Here is your cipher fragment for Slot #5:

KEY SHARD #5

Slot 5 of 8

🧩 Data, Spreadsheets & Science Key Ring Tracker:Slot #5 Active

#1?

#2?

#3?

#4?

#55

#6?

#7?

#8?

Go to Data, Spreadsheets & Science Boss Vault➔

Level 1

AutoSum & Addition

Using AutoSum and simple addition formulas.

Learning objective

Learning About: AutoSum. Learning To: Add numbers. Learning To Be: Inquirer.

Let's Go!

Learn

Instead of adding numbers on paper, spreadsheets do the math for you! By using the SUM formula, the computer adds up entire columns instantly and updates the answer if any number changes. In computing, mathematical tools allow us to combine and count items efficiently. As you explore the interactive Spreadsheet Simulator below, focus on how to type a basic SUM formula (=SUM(A1:A5)) to calculate totals automatically.

Interactive Simulator Sandbox

📊

Spreadsheet StudioStep 1 • Bronze Tier

A1fx

	A	B
1	Item	Amount
2	Apples	5
3	Bananas	3
4	Carrots	2
5	Total

Challenge: Click cell B5 to inspect how the AutoSum formula adds up column B!

Apply Simulator Skills • Try it yourself

Sum up the totals!

🥉 Bronze Transfer • Task 1Open your sample spreadsheet in your workspace to establish the foundational setup for autosum & addition.

🥈 Silver Transfer • Task 2Select cell A6 beneath the column of sales figures carefully, checking your settings and controls as you build your project.

🥇 Gold Transfer • Task 3Click the AutoSum (Σ) button or type =SUM(A1:A5) and verify that your output meets the target goal for autosum & addition.

✨ Extension Task • Task 3+Press Enter and change one number above to watch it update.

🚀 Open a practice space

📊Open Google Sheets

Or try locally: 💻 LibreOffice Calc (free)

🌟 Student Project GuideSuccess Criteria & WAGOLL (What A Good One Looks Like)▾

🎯 Task Success Criteria (Rubric)

Identify numbers arranged in a spreadsheet column.

Locate and click the AutoSum button (Σ) to add a column.

Type a basic SUM formula (=SUM(A1:A5)) to calculate totals automatically.

Explain how changing a cell value automatically updates the SUM total.

👀 What A Good One Looks Like (WAGOLL)

Exemplar 1 of 2

⭐ Mastering Standard

Formulas & Math Exemplar: Demonstration of mastering quality standards for AutoSum & Addition.

⭐ Mastering Standard

Formulas & Math Exemplar: Demonstration of mastering quality standards for AutoSum & Addition.

📁

Save to Your Website Portfolio:

When you finish creating your project in your software, copy the share link or take a screenshot and publish it onto your student portfolio website!

Reflect

Learning check

Which symbol must EVERY formula or function in a spreadsheet start with?

Select the matching icon or option below:

➕Add🟰Equals⭐Star❓Question

Answer correctly to unlock the next level.

Teacher setup, curriculum links and progress descriptors

Spark support

Routine: See, Think, Wonder

Teacher notes: Facilitate the See, Think, Wonder routine for 2–3 minutes. Encourage students to connect the prompt to Form (Mathematical tools allow us to combine and count items efficiently.).

Achievement pathway

Foundation: Identify numbers arranged in a spreadsheet column.
Developing: Locate and click the AutoSum button (Σ) to add a column.
Secure: Type a basic SUM formula (=SUM(A1:A5)) to calculate totals automatically.
Mastering: Explain how changing a cell value automatically updates the SUM total.

Curriculum links

Curriculum strand: MAT.01.C.1.4

Outcome: SPFN-O-01 — Using AutoSum and simple addition formulas.

PYP: Form · Mathematical tools allow us to combine and count items efficiently.

Learner profile: Inquirer

Competency tags

formulas
autosum
addition
spreadsheets

Gate support

Accessibility alternative: Students may verbally explain or point to the correct answer for "Which symbol must EVERY formula or function in a spreadsheet start with?" with a teacher or teaching assistant.

Teacher override: allow

Locked level

Level 2: Basic Math Operators

Complete the previous learning check to unlock this next level.

Level 2

Basic Math Operators

Using standard operators (+, -, *, /) for calculations.

Learning objective

Learning About: Operators. Learning To: Calculate cell math. Learning To Be: Communicator.

Let's Go!

Learn

Computers use special key symbols for math: asterisk (*) means multiply, and forward slash (/) means divide. By referencing cell names like =A2*B2, you can build dynamic budget calculators! In computing, calculations give us precise insights to solve real-world problems. As you explore the interactive Python Console Simulator below, focus on how to calculate total costs by multiplying Quantity by Price (=A2*B2).

Interactive Simulator Sandbox

🐍main.pyClean

Snippets:

Target: Type print("Hello World!") and run your script.

Interactive Python REPL (v3.12.2)

Python 3.12.2 (tags/v3.12.2:5acc80f, Feb 6 2024, 01:22:40) [MSC v.1938 64 bit (AMD64)]

Type "help", "copyright", "credits" or "license" for more information.

>>> Ready to execute scripts.

🌲

AST Root NodeModuleAbstract Syntax Tree representation.

🏷️

Symbol Scope0 IdentifiersGlobal variable memory allocations.

🛡️

Linter & PEP 8CleanZero syntax or indentation warnings.

⚡

Complexity & LoopsO(1)Bounded iteration & recursion safety.

Python Abstract Syntax Tree (AST)Valid AST

Type Python code in the IDE to view real-time AST node tree parsing.

Runtime Variable Symbol TableStack Memory

Identifier	Type	Value	Scope
No active variables in memory. Assign variables (e.g. score = 100) to inspect.

Apply Simulator Skills • Try it yourself

Build a receipt calculator!

🥉 Bronze Transfer • Task 4Open the shopping list spreadsheet in your workspace to establish the foundational setup for basic math operators.

🥈 Silver Transfer • Task 5In Column C, type =A2*B2 to multiply Quantity by Unit Price.

🥇 Gold Transfer • Task 6Fill the formula down Column C and verify that your output meets the target goal for basic math operators.

✨ Extension Task • Task 6+Add a total row at the bottom using =SUM(C2:C6) and review your finished work so it is ready to share in your digital portfolio.

🚀 Open a practice space

📊Open Google Sheets

Or try locally: 💻 LibreOffice Calc (free)

🌟 Student Project GuideSuccess Criteria & WAGOLL (What A Good One Looks Like)▾

🎯 Task Success Criteria (Rubric)

Recognise the operators +, -, *, and / in spreadsheets.

Write formulas using cell references like =A1*B1 or =A1-B1.

Calculate total costs by multiplying Quantity by Price (=A2*B2).

Troubleshoot formula errors like #VALUE! caused by text in number cells.

👀 What A Good One Looks Like (WAGOLL)

Exemplar 1 of 2

⭐ Mastering Standard

Formulas & Math Exemplar: Demonstration of mastering quality standards for Basic Math Operators.

⭐ Mastering Standard

Formulas & Math Exemplar: Demonstration of mastering quality standards for Basic Math Operators.

📁

Save to Your Website Portfolio:

When you finish creating your project in your software, copy the share link or take a screenshot and publish it onto your student portfolio website!

Reflect

Learning check

Which symbol does a spreadsheet use to MULTIPLY two cell values together?

Choose the correct operator.

x+*/

Answer correctly to unlock the next level.

Teacher setup, curriculum links and progress descriptors

Spark support

Routine: Zoom In

Teacher notes: Facilitate the Zoom In routine for 2–3 minutes. Encourage students to connect the prompt to Function (Calculations give us precise insights to solve real-world problems.).

Achievement pathway

Foundation: Recognise the operators +, -, *, and / in spreadsheets.
Developing: Write formulas using cell references like =A1*B1 or =A1-B1.
Secure: Calculate total costs by multiplying Quantity by Price (=A2*B2).
Mastering: Troubleshoot formula errors like #VALUE! caused by text in number cells.

Curriculum links

Curriculum strand: MAT.02.C.1.4

Outcome: SPFN-O-02 — Using standard operators (+, -, *, /) for calculations.

PYP: Function · Calculations give us precise insights to solve real-world problems.

Learner profile: Communicator

Competency tags

operators
multiplication
division
cell-referencing

Gate support

Accessibility alternative: Students may verbally explain or point to the correct answer for "Which symbol does a spreadsheet use to MULTIPLY two cell values together?" with a teacher or teaching assistant.

Teacher override: allow

Locked level

Level 3: AVERAGE, MAX & MIN

Complete the previous learning check to unlock this next level.

Level 3

AVERAGE, MAX & MIN

Using statistical summary functions.

Learning objective

Learning About: Statistics. Learning To: Summarise data. Learning To Be: Thinker.

Let's Go!

Learn

Scanning hundreds of rows to find the highest or lowest number takes time. Statistical functions like =AVERAGE(), =MAX(), and =MIN() instantly summarize massive datasets into key insights! In computing, data summarisation helps us compare sets and identify extremes. As you explore the interactive Diagram Builder Simulator below, focus on how to find the highest (=MAX) and lowest (=MIN) test scores or temperatures in a dataset.

Interactive Simulator Sandbox

⚙️ Flow Inspector

Total Nodes: 0

Connections: 0

Cyclomatic Complexity: 1 (Linear)

Place shapes from palette to begin constructing your diagram.

💻 Synced Pseudocode:

// Flowchart execution logic
BEGIN Process
  // Add nodes to generate code
END

Quick Blueprints:

Apply Simulator Skills • Try it yourself

Analyze class weather data!

🥉 Bronze Transfer • Task 7Open the temperature log in your workspace to establish the foundational setup for average, max & min.

🥈 Silver Transfer • Task 8Use =AVERAGE(B2:B31) to find the average weekly temperature.

🥇 Gold Transfer • Task 9Use =MAX(B2:B31) to identify the hottest day and verify that your output meets the target goal for average, max & min.

✨ Extension Task • Task 9+Use =MIN(B2:B31) for the coldest day and review your finished work so it is ready to share in your digital portfolio.

🚀 Open a practice space

📊Open Google Sheets

Or try locally: 💻 LibreOffice Calc (free)

🌟 Student Project GuideSuccess Criteria & WAGOLL (What A Good One Looks Like)▾

🎯 Task Success Criteria (Rubric)

Explain what AVERAGE, MAX, and MIN functions calculate.

Enter =AVERAGE(range) to calculate the mean of a set of scores.

Find the highest (=MAX) and lowest (=MIN) test scores or temperatures in a dataset.

Compare averages between two different groups to draw conclusions.

👀 What A Good One Looks Like (WAGOLL)

Exemplar 1 of 2

⭐ Mastering Standard

Formulas & Math Exemplar: Demonstration of mastering quality standards for AVERAGE, MAX & MIN.

⭐ Mastering Standard

Formulas & Math Exemplar: Demonstration of mastering quality standards for AVERAGE, MAX & MIN.

📁

Save to Your Website Portfolio:

When you finish creating your project in your software, copy the share link or take a screenshot and publish it onto your student portfolio website!

Reflect

Learning check

Which formula should you use to find the HIGHEST score in the cell range A1 to A20?

Select the correct function.

=HIGH(A1:A20)=TOP(A1:A20)=MAX(A1:A20)=SUM(A1:A20)

Answer correctly to unlock the next level.

Teacher setup, curriculum links and progress descriptors

Spark support

Routine: Think, Puzzle, Explore

Teacher notes: Facilitate the Think, Puzzle, Explore routine for 2–3 minutes. Encourage students to connect the prompt to Connection (Data summarisation helps us compare sets and identify extremes.).

Achievement pathway

Foundation: Explain what AVERAGE, MAX, and MIN functions calculate.
Developing: Enter =AVERAGE(range) to calculate the mean of a set of scores.
Secure: Find the highest (=MAX) and lowest (=MIN) test scores or temperatures in a dataset.
Mastering: Compare averages between two different groups to draw conclusions.

Curriculum links

Curriculum strand: MAT.02.D.4.1

Outcome: SPFN-O-03 — Using statistical summary functions.

PYP: Connection · Data summarisation helps us compare sets and identify extremes.

Learner profile: Thinker

Competency tags

average
max
min
statistics

Gate support

Accessibility alternative: Students may verbally explain or point to the correct answer for "Which formula should you use to find the HIGHEST score in the cell range A1 to A20?" with a teacher or teaching assistant.

Teacher override: allow

Locked level

Level 4: COUNT & COUNTIF

Complete the previous learning check to unlock this next level.

Level 4

COUNT & COUNTIF

Counting items conditionally with COUNTIF.

Learning objective

Learning About: Conditional Counts. Learning To: Count matching items. Learning To Be: Principled.

Let's Go!

Learn

Manual counting leads to human mistakes! The =COUNTIF(range, "criteria") function scans your data automatically and counts only the cells that match your exact condition. In computing, categorising and counting data items helps us understand community trends. As you explore the interactive Microcontroller Simulator below, focus on how to analyze survey results by counting how many students chose each option.

Interactive Simulator Sandbox

micro:bit

📡 RF

GND

🔊Buzzer:Silent

⚙️Actuator:Centered (90°)

📡Radio Packet:Ch: 1 (Idle)

Target: Click the LEDs to draw an icon or trigger hardware inputs.

⚡

GPIO Logic Voltage3.3 V LogicEdge pin logic level reference.

〰️

PWM Duty Cycle50.0% (1.5ms)Servo Pulse Width Modulation.

🛡️

Circuit SafetySafe / NormalZero short circuits between 3V and GND.

📻

RF 2.4GHz BandConnectedBroadcast packet telemetry stream.

Sensor Raw ADC & I2C Telemetry10-Bit ADC

Sensor Input	Bus Type	Raw Digital	Converted Unit
☀️ Photocell (Light)	Internal Matrix	128	50% Lux
🌡️ Thermistor (Temp)	I2C Subsystem	490	24°C
📳 Accelerometer (X,Y,Z)	LSM303AGR I2C	(0, 0, 1024)	1.0g Flat
🔌 GPIO Pin 0	Edge Connector	0	0.0V (Low)

Oscilloscope Signal Waveform50 Hz PWM

CH1: GPIO Pin 0 (PWM Servo) | 20ms/div | 3.3V pk-pk

Apply Simulator Skills • Try it yourself

Count survey votes automatically!

🥉 Bronze Transfer • Task 10Open the lunch survey spreadsheet in your workspace to establish the foundational setup for count & countif.

🥈 Silver Transfer • Task 11In cell E2, enter =COUNTIF(B2:B50, "Pizza") to count Pizza votes.

🥇 Gold Transfer • Task 12Repeat in cell E3 for "Pasta" and verify that your output meets the target goal for count & countif.

✨ Extension Task • Task 12+Verify that total counts match overall responses and review your finished work so it is ready to share in your digital portfolio.

🚀 Open a practice space

📊Open Google Sheets

Or try locally: 💻 LibreOffice Calc (free)

🌟 Student Project GuideSuccess Criteria & WAGOLL (What A Good One Looks Like)▾

🎯 Task Success Criteria (Rubric)

Differentiate between COUNT (numbers) and COUNTA (text/all entries).

Use =COUNTIF(range, criteria) to count cells that match a specific word (e.g., 'Yes').

Analyze survey results by counting how many students chose each option.

Explain how COUNTIF differs from SUMIF when processing sales data.

👀 What A Good One Looks Like (WAGOLL)

Exemplar 1 of 2

⭐ Mastering Standard

Formulas & Math Exemplar: Demonstration of mastering quality standards for COUNT & COUNTIF.

⭐ Mastering Standard

Formulas & Math Exemplar: Demonstration of mastering quality standards for COUNT & COUNTIF.

📁

Save to Your Website Portfolio:

When you finish creating your project in your software, copy the share link or take a screenshot and publish it onto your student portfolio website!

Reflect

Learning check

What formula correctly counts how many cells in range A1:A30 contain the word 'Pass'?

Select the correct formula syntax.

=COUNT(A1:A30, "Pass")=COUNTIF(A1:A30, "Pass")=SUMIF(A1:A30, "Pass")=TOTALIF(A1:A30, "Pass")

Answer correctly to unlock the next level.

Teacher setup, curriculum links and progress descriptors

Spark support

Routine: See, Think, Wonder

Teacher notes: Facilitate the See, Think, Wonder routine for 2–3 minutes. Encourage students to connect the prompt to Responsibility (Categorising and counting data items helps us understand community trends.).

Achievement pathway

Foundation: Differentiate between COUNT (numbers) and COUNTA (text/all entries).
Developing: Use =COUNTIF(range, criteria) to count cells that match a specific word (e.g., 'Yes').
Secure: Analyze survey results by counting how many students chose each option.
Mastering: Explain how COUNTIF differs from SUMIF when processing sales data.

Curriculum links

Curriculum strand: CM.02.B.1.1

Outcome: SPFN-O-04 — Counting items conditionally with COUNTIF.

PYP: Responsibility · Categorising and counting data items helps us understand community trends.

Learner profile: Principled

Competency tags

count
countif
criteria
survey-analysis

Gate support

Accessibility alternative: Students may verbally explain or point to the correct answer for "What formula correctly counts how many cells in range A1:A30 contain the word 'Pass'?" with a teacher or teaching assistant.

Teacher override: allow

Locked level

Level 5: Logical IF Statements

Complete the previous learning check to unlock this next level.

Level 5

Logical IF Statements

Automating decisions using IF logic.

Learning objective

Learning About: IF Logic. Learning To: Automate choices. Learning To Be: Knowledgeable.

Let's Go!

Learn

The =IF(logical_test, value_if_true, value_if_false) statement allows the spreadsheet to think! If a student's score is 50 or higher, it outputs 'Pass'; otherwise, it outputs 'Needs Retake'. In computing, automated logic enables systems to evaluate criteria and flag exceptions. As you explore the interactive Formatting Toolbar Simulator below, focus on how to combine IF with comparison operators (>, <, >=, <=, <>).

Interactive Simulator Sandbox

Words: 0Characters: 0Estimated Speed: 0 WPM

💾 All changes saved to Drive

Apply Simulator Skills • Try it yourself

Automate grading feedback!

🥉 Bronze Transfer • Task 13Open the gradebook spreadsheet in your workspace to establish the foundational setup for logical if statements.

🥈 Silver Transfer • Task 14In Column C, type =IF(B2>=70, "Distinction", "Standard").

🥇 Gold Transfer • Task 15Drag the fill handle down Column C and verify that your output meets the target goal for logical if statements.

✨ Extension Task • Task 15+Change one student's mark to test if the status flips automatically.

🚀 Open a practice space

📊Open Google Sheets

Or try locally: 💻 LibreOffice Calc (free)

🌟 Student Project GuideSuccess Criteria & WAGOLL (What A Good One Looks Like)▾

🎯 Task Success Criteria (Rubric)

Explain the 3 parts of an IF function: Logical Test, Value If True, Value If False.

Write simple IF statements like =IF(A2>=50, "Pass", "Fail").

Combine IF with comparison operators (>, <, >=, <=, <>).

Nest simple IF statements or use AND/OR to evaluate complex grading systems.

👀 What A Good One Looks Like (WAGOLL)

Exemplar 1 of 2

⭐ Mastering Standard

Formulas & Math Exemplar: Demonstration of mastering quality standards for Logical IF Statements.

⭐ Mastering Standard

Formulas & Math Exemplar: Demonstration of mastering quality standards for Logical IF Statements.

📁

Save to Your Website Portfolio:

When you finish creating your project in your software, copy the share link or take a screenshot and publish it onto your student portfolio website!

Reflect

Learning check

Which formula outputs 'Yes' if cell A1 is greater than 100, and 'No' if it is not?

Choose the correct IF statement syntax.

=IF(A1>100, "Yes", "No")=IF(A1>100, "No", "Yes")=CHECK(A1>100, "Yes", "No")=IF(A1=100, "Yes")

Answer correctly to unlock the next level.

Teacher setup, curriculum links and progress descriptors

Spark support

Routine: I used to think...

Teacher notes: Facilitate the I used to think... routine for 2–3 minutes. Encourage students to connect the prompt to Perspective (Automated logic enables systems to evaluate criteria and flag exceptions.).

Achievement pathway

Foundation: Explain the 3 parts of an IF function: Logical Test, Value If True, Value If False.
Developing: Write simple IF statements like =IF(A2>=50, "Pass", "Fail").
Secure: Combine IF with comparison operators (>, <, >=, <=, <>).
Mastering: Nest simple IF statements or use AND/OR to evaluate complex grading systems.

Curriculum links

Curriculum strand: CM.03.B.1.3

Outcome: SPFN-O-05 — Automating decisions using IF logic.

PYP: Perspective · Automated logic enables systems to evaluate criteria and flag exceptions.

Learner profile: Knowledgeable

Competency tags

if-statement
logical-tests
boolean
automation

Gate support

Accessibility alternative: Students may verbally explain or point to the correct answer for "Which formula outputs 'Yes' if cell A1 is greater than 100, and 'No' if it is not?" with a teacher or teaching assistant.

Teacher override: allow

Locked level

Level 6: VLOOKUP & Lookups

Complete the previous learning check to unlock this next level.

Level 6

VLOOKUP & Lookups

Connecting relational data using VLOOKUP/XLOOKUP.

Learning objective

Learning About: Lookups. Learning To: Retrieve linked data. Learning To Be: Reflective.

Let's Go!

Learn

In real-world databases, tables are linked together! VLOOKUP searches a key (like a Student ID or Barcode) in a master table and automatically pulls back the correct name or price into your active sheet. In computing, relational data structures allow organisations to manage interconnected information efficiently. As you explore the interactive Block Coding Simulator below, focus on how to implement exact match lookups (FALSE / 0) and handle #N/A errors using IFERROR.

Interactive Simulator Sandbox

🧩Script Workspace is Empty

Select blocks from the palette on the left to build your program stack.

Target: Assemble the required blocks and run the script.

🌲

AST StructureEmpty StackStack must initiate with an Event Hat block.

⚡

Execution FlowSequentialChecks linear vs iterative control branches.

🔄

Loop SafetySafeZero infinite loop hanging detected.

🧬

Clone Lifecycle0 ActiveClean garbage collection via delete-clone.

Block Tree Structure & GrammarValid Grammar

Assemble blocks in the Studio to inspect the syntax tree.

Runtime Trace Log & Performance60 FPS

Step	Block Instruction	Sprite (X, Y)	Variables	Status
No script execution traces yet. Click 🚩 Go to record runtime data.

Apply Simulator Skills • Try it yourself

Build an automated product lookup!

🥉 Bronze Transfer • Task 16Open the inventory & order sheet

🥈 Silver Transfer • Task 17In cell C2, enter =VLOOKUP(B2, 'Master Catalog'!A:C, 2, FALSE) to pull product names

🥇 Gold Transfer • Task 18Wrap in =IFERROR(..., "Not Found") to clean up missing items

✨ Extension Task • Task 18+Test typing different product IDs

🚀 Open a practice space

📊Open Google Sheets

Or try locally: 💻 LibreOffice Calc (free)

🌟 Student Project GuideSuccess Criteria & WAGOLL (What A Good One Looks Like)▾

🎯 Task Success Criteria (Rubric)

Explain why looking up data across separate tables prevents duplicate data entry.

Use =VLOOKUP(lookup_value, table_array, col_index, FALSE) to fetch student IDs or prices.

Implement exact match lookups (FALSE / 0) and handle #N/A errors using IFERROR.

Compare VLOOKUP vs XLOOKUP for flexible multi-directional searching.

👀 What A Good One Looks Like (WAGOLL)

Exemplar 1 of 2

⭐ Mastering Standard

Formulas & Math Exemplar: Demonstration of mastering quality standards for VLOOKUP & Lookups.

⭐ Mastering Standard

Formulas & Math Exemplar: Demonstration of mastering quality standards for VLOOKUP & Lookups.

📁

Save to Your Website Portfolio:

When you finish creating your project in your software, copy the share link or take a screenshot and publish it onto your student portfolio website!

Reflect

Learning check

What should the final parameter (range_lookup) in a VLOOKUP formula be set to when searching for an EXACT ID match?

Choose the correct parameter for exact matching.

TRUEFALSE (or 0)EXACTMATCH

Answer correctly to complete this strand.

Teacher setup, curriculum links and progress descriptors

Spark support

Routine: Step Inside

Teacher notes: Facilitate the Step Inside routine for 2–3 minutes. Encourage students to connect the prompt to Reflection (Relational data structures allow organisations to manage interconnected information efficiently.).

Achievement pathway

Foundation: Explain why looking up data across separate tables prevents duplicate data entry.
Developing: Use =VLOOKUP(lookup_value, table_array, col_index, FALSE) to fetch student IDs or prices.
Secure: Implement exact match lookups (FALSE / 0) and handle #N/A errors using IFERROR.
Mastering: Compare VLOOKUP vs XLOOKUP for flexible multi-directional searching.

Curriculum links

Curriculum strand: MAT.03.E.1.6

Outcome: SPFN-O-06 — Connecting relational data using VLOOKUP/XLOOKUP.

PYP: Reflection · Relational data structures allow organisations to manage interconnected information efficiently.

Learner profile: Reflective

Competency tags

vlookup
xlookup
relational-data
data-retrieval

Gate support

Accessibility alternative: Students may verbally explain or point to the correct answer for "What should the final parameter (range_lookup) in a VLOOKUP formula be set to when searching for an EXACT ID match?" with a teacher or teaching assistant.

Teacher override: allow

🎒 Students Home →

Spreadsheet Functions

Spreadsheet Functions

AutoSum & Addition

Basic Math Operators

AVERAGE, MAX & MIN

COUNT & COUNTIF

Logical IF Statements

VLOOKUP & Lookups

Stage Mastery Quest Challenge

Spreadsheet Functions

What is your next action?

Question Prompt goes here?

STRAND MASTERY ACHIEVED!

Area Boss Cipher Fragment Discovered!

🎉 Mastered! Keep Practicing!

Learning About: AutoSum. Learning To: Add numbers. Learning To Be: Inquirer.

Data Visualization Chart

Sum up the totals!

🎯 Task Success Criteria (Rubric)

👀 What A Good One Looks Like (WAGOLL)

Self-Assessment & Reflection

Which symbol must EVERY formula or function in a spreadsheet start with?

Spark support

Achievement pathway

Curriculum links

Competency tags

Gate support

Level 2: Basic Math Operators

Learning About: Operators. Learning To: Calculate cell math. Learning To Be: Communicator.

Build a receipt calculator!

🎯 Task Success Criteria (Rubric)

👀 What A Good One Looks Like (WAGOLL)

Self-Assessment & Reflection

Which symbol does a spreadsheet use to MULTIPLY two cell values together?

Spark support

Achievement pathway

Curriculum links

Competency tags

Gate support

Level 3: AVERAGE, MAX & MIN

Learning About: Statistics. Learning To: Summarise data. Learning To Be: Thinker.

🧠 Hierarchical Mind Map & Concept Mapping Studio

⭕ 2-Circle Venn Diagram & Hierarchical Classification Tree

📐 Flowchart Logic, Venn Sets & CAD Schematic Audit

1. Standard ISO Flowchart Symbols

2. Visual Hierarchy & Linking Phrases

3. Mathematical Set Intersections

4. Top-Down Modular Decomposition

📋 Diagram Engineering Quality Checklist

Analyze class weather data!

🎯 Task Success Criteria (Rubric)

👀 What A Good One Looks Like (WAGOLL)

Self-Assessment & Reflection

Which formula should you use to find the HIGHEST score in the cell range A1 to A20?

Spark support

Achievement pathway

Curriculum links

Competency tags

Gate support

Level 4: COUNT & COUNTIF

Learning About: Conditional Counts. Learning To: Count matching items. Learning To Be: Principled.

Count survey votes automatically!

🎯 Task Success Criteria (Rubric)

👀 What A Good One Looks Like (WAGOLL)

Self-Assessment & Reflection

What formula correctly counts how many cells in range A1:A30 contain the word 'Pass'?

Spark support

Achievement pathway

Curriculum links

Competency tags

Gate support

Level 5: Logical IF Statements

Learning About: IF Logic. Learning To: Automate choices. Learning To Be: Knowledgeable.

📊 Document Analytics & Typing Proficiency

📋 Curriculum Digital Writing Checklist

Automate grading feedback!

🎯 Task Success Criteria (Rubric)

👀 What A Good One Looks Like (WAGOLL)

Self-Assessment & Reflection

Which formula outputs 'Yes' if cell A1 is greater than 100, and 'No' if it is not?