๐
Learning objective
Formulas & Math
Spreadsheet Functions
1
2
3
4
5
6
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.
AutoSum & Addition
Using AutoSum and simple addition formulas.
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.
| A | B | C | |
|---|---|---|---|
| 1 | Item | Amount | |
| 2 | Apples | 5 | |
| 3 | Bananas | 3 | |
| 4 | Carrots | 2 | |
| 5 | Total | =SUM(B2:B4) |
Try it yourself
Sum up the totals!
๐ Open a practice space
Or try locally: ๐ป LibreOffice Calc (free)
๐ Student Project GuideSuccess Criteria & WAGOLL (What A Good One Looks Like)
๐ฏ Task Success Criteria (Rubric)
Foundation
Identify numbers arranged in a spreadsheet column.
Developing
Locate and click the AutoSum button (ฮฃ) to add a column.
Secure (Goal)
Type a basic SUM formula (=SUM(A1:A5)) to calculate totals automatically.
Mastery
Explain how changing a cell value automatically updates the SUM total.
๐ What A Good One Looks Like (WAGOLL)
Exemplar Standard
A top-tier student project for AutoSum & Addition includes:
- Core Deliverable: Sum up the totals!
- Target Quality: Type a basic SUM formula (=SUM(A1:A5)) to calculate totals automatically.
- Excellence & Polish: Explain how changing a cell value automatically updates the SUM total.
๐
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!
โญSelf-Assessment: How confident do you feel with this skill?
Reflect
Learning check
Teacher setup, curriculum links and progress descriptors
Spark support
Routine: See, Think, Wonder
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
Gate support
Accessibility alternative:
Teacher override: allow
Locked level
Level 2: Basic Math Operators
Complete the previous learning check to unlock this next level.
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!
| A | B | C | |
|---|---|---|---|
| 1 | Item | Amount | |
| 2 | Apples | 5 | |
| 3 | Bananas | 3 | |
| 4 | Carrots | 2 | |
| 5 | Total | =SUM(B2:B4) |
๐
Data Visualization Chart
Try it yourself
Build a receipt calculator!
๐ Open a practice space
Or try locally: ๐ป LibreOffice Calc (free)
๐ Student Project GuideSuccess Criteria & WAGOLL (What A Good One Looks Like)
๐ฏ Task Success Criteria (Rubric)
Foundation
Recognise the operators +, -, *, and / in spreadsheets.
Developing
Write formulas using cell references like =A1*B1 or =A1-B1.
Secure (Goal)
Calculate total costs by multiplying Quantity by Price (=A2*B2).
Mastery
Troubleshoot formula errors like #VALUE! caused by text in number cells.
๐ What A Good One Looks Like (WAGOLL)
Exemplar Standard
A top-tier student project for Basic Math Operators includes:
- Core Deliverable: Build a receipt calculator!
- Target Quality: Calculate total costs by multiplying Quantity by Price (=A2*B2).
- Excellence & Polish: Troubleshoot formula errors like #VALUE! caused by text in number cells.
๐
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!
โญSelf-Assessment: How confident do you feel with this skill?
Reflect
Learning check
Teacher setup, curriculum links and progress descriptors
Spark support
Routine: Zoom In
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
Gate support
Accessibility alternative:
Teacher override: allow
Locked level
Level 3: AVERAGE, MAX & MIN
Complete the previous learning check to unlock this next level.
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!
| A | B | C | |
|---|---|---|---|
| 1 | Item | Amount | |
| 2 | Apples | 5 | |
| 3 | Bananas | 3 | |
| 4 | Carrots | 2 | |
| 5 | Total | =SUM(B2:B4) |
๐
Data Visualization Chart
Try it yourself
Analyze class weather data!
๐ Open a practice space
Or try locally: ๐ป LibreOffice Calc (free)
๐ Student Project GuideSuccess Criteria & WAGOLL (What A Good One Looks Like)
๐ฏ Task Success Criteria (Rubric)
Foundation
Explain what AVERAGE, MAX, and MIN functions calculate.
Developing
Enter =AVERAGE(range) to calculate the mean of a set of scores.
Secure (Goal)
Find the highest (=MAX) and lowest (=MIN) test scores or temperatures in a dataset.
Mastery
Compare averages between two different groups to draw conclusions.
๐ What A Good One Looks Like (WAGOLL)
Exemplar Standard
A top-tier student project for AVERAGE, MAX & MIN includes:
- Core Deliverable: Analyze class weather data!
- Target Quality: Find the highest (=MAX) and lowest (=MIN) test scores or temperatures in a dataset.
- Excellence & Polish: Compare averages between two different groups to draw conclusions.
๐
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!
โญSelf-Assessment: How confident do you feel with this skill?
Reflect
Learning check
Teacher setup, curriculum links and progress descriptors
Spark support
Routine: Think, Puzzle, Explore
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
Gate support
Accessibility alternative:
Teacher override: allow
Locked level
Level 4: COUNT & COUNTIF
Complete the previous learning check to unlock this next level.
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.
| A | B | C | |
|---|---|---|---|
| 1 | Item | Amount | |
| 2 | Apples | 5 | |
| 3 | Bananas | 3 | |
| 4 | Carrots | 2 | |
| 5 | Total | =SUM(B2:B4) |
๐
Data Visualization Chart
Try it yourself
Count survey votes automatically!
๐ Open a practice space
Or try locally: ๐ป LibreOffice Calc (free)
๐ Student Project GuideSuccess Criteria & WAGOLL (What A Good One Looks Like)
๐ฏ Task Success Criteria (Rubric)
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 (Goal)
Analyze survey results by counting how many students chose each option.
Mastery
Explain how COUNTIF differs from SUMIF when processing sales data.
๐ What A Good One Looks Like (WAGOLL)
Exemplar Standard
A top-tier student project for COUNT & COUNTIF includes:
- Core Deliverable: Count survey votes automatically!
- Target Quality: Analyze survey results by counting how many students chose each option.
- Excellence & Polish: Explain how COUNTIF differs from SUMIF when processing sales data.
๐
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!
โญSelf-Assessment: How confident do you feel with this skill?
Reflect
Learning check
Teacher setup, curriculum links and progress descriptors
Spark support
Routine: See, Think, Wonder
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
Gate support
Accessibility alternative:
Teacher override: allow
Locked level
Level 5: Logical IF Statements
Complete the previous learning check to unlock this next level.
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'.
| A | B | C | |
|---|---|---|---|
| 1 | Item | Amount | |
| 2 | Apples | 5 | |
| 3 | Bananas | 3 | |
| 4 | Carrots | 2 | |
| 5 | Total | =SUM(B2:B4) |
๐
Data Visualization Chart
Try it yourself
Automate grading feedback!
๐ Open a practice space
Or try locally: ๐ป LibreOffice Calc (free)
๐ Student Project GuideSuccess Criteria & WAGOLL (What A Good One Looks Like)
๐ฏ Task Success Criteria (Rubric)
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 (Goal)
Combine IF with comparison operators (>, <, >=, <=, <>).
Mastery
Nest simple IF statements or use AND/OR to evaluate complex grading systems.
๐ What A Good One Looks Like (WAGOLL)
Exemplar Standard
A top-tier student project for Logical IF Statements includes:
- Core Deliverable: Automate grading feedback!
- Target Quality: Combine IF with comparison operators (>, <, >=, <=, <>).
- Excellence & Polish: Nest simple IF statements or use AND/OR to evaluate complex grading systems.
๐
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!
โญSelf-Assessment: How confident do you feel with this skill?
Reflect
Learning check
Teacher setup, curriculum links and progress descriptors
Spark support
Routine: I used to think...
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
Gate support
Accessibility alternative:
Teacher override: allow
Locked level
Level 6: VLOOKUP & Lookups
Complete the previous learning check to unlock this next level.
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.
| A | B | C | |
|---|---|---|---|
| 1 | Item | Amount | |
| 2 | Apples | 5 | |
| 3 | Bananas | 3 | |
| 4 | Carrots | 2 | |
| 5 | Total | =SUM(B2:B4) |
๐
Data Visualization Chart
Try it yourself
Build an automated product lookup!
๐ Open a practice space
Or try locally: ๐ป LibreOffice Calc (free)
๐ Student Project GuideSuccess Criteria & WAGOLL (What A Good One Looks Like)
๐ฏ Task Success Criteria (Rubric)
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 (Goal)
Implement exact match lookups (FALSE / 0) and handle #N/A errors using IFERROR.
Mastery
Compare VLOOKUP vs XLOOKUP for flexible multi-directional searching.
๐ What A Good One Looks Like (WAGOLL)
Exemplar Standard
A top-tier student project for VLOOKUP & Lookups includes:
- Core Deliverable: Build an automated product lookup!
- Target Quality: Implement exact match lookups (FALSE / 0) and handle #N/A errors using IFERROR.
- Excellence & Polish: Compare VLOOKUP vs XLOOKUP for flexible multi-directional searching.
๐
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!
โญSelf-Assessment: How confident do you feel with this skill?
Reflect
Learning check
Teacher setup, curriculum links and progress descriptors
Spark support
Routine: Step Inside
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
Gate support
Accessibility alternative:
Teacher override: allow