Module 5 – Spreadsheets
Lesson 4 – Functions
Functions are built-in formulas that perform calculations automatically. They save time, reduce errors and are essential for accurate data analysis. This lesson covers the most common ICDL spreadsheet functions: SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, and COUNTBLANK.
Module 5 – Spreadsheets
Lesson 4 – Functions
Functions are built-in formulas used to perform calculations automatically. They save time, reduce mistakes and are essential for analysing data correctly.
This lesson covers all ICDL-required functions:
SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, COUNTBLANK.
1. What is a function?
A function follows this structure:
=FUNCTION_NAME(arguments)
Examples:
- =SUM(A1:A5)
- =AVERAGE(B1:B10)
- =MAX(C2:C20)
Quick check: Which part of the function =SUM(A1:A5) is the argument?
Show answer
A1:A5 is the argument — it tells the function what to calculate.
2. SUM Function
Adds all numbers in a range.
Example: =SUM(A1:A10)
Used for:
- Total sales
- Total hours
- Total expenses
Try it: Type numbers into A1–A5, then in A6 enter: =SUM(A1:A5) Does the total update if you change a value?
Show answer
Yes — SUM updates automatically.
3. AVERAGE Function
Calculates the mean (average).
Example: =AVERAGE(B1:B12)
Quick question: If B1–B4 contain 2, 4, 6, 8, what is the result of =AVERAGE(B1:B4)?
5
4. MIN and MAX
- =MIN(range) → smallest value
- =MAX(range) → largest value
Mini-task: Enter values 12, 45, 7, 30. Which function shows 7?
Show answer
MIN shows the smallest value.
5. COUNT, COUNTA, COUNTBLANK
- COUNT – counts numbers only
- COUNTA – counts anything that isn’t empty
- COUNTBLANK – counts empty cells
Try it: Fill cells A1–A5 with some text & numbers. Then test:
- =COUNT(A1:A5)
- =COUNTA(A1:A5)
- =COUNTBLANK(A1:A5)
6. AutoSum
AutoSum inserts a SUM formula automatically.
Steps:
- Select a cell under your column of numbers
- Click the AutoSum (Σ) button
- Press Enter
Quick check: What function does AutoSum insert?
SUM
7. Function Arguments
Arguments tell the function what to calculate.
Examples:
- =SUM(A1:A10) → One argument (a range)
- =SUM(A1, B1, C1) → Multiple arguments
8. Combining Functions
You can put functions inside other functions.
Example:=AVERAGE(SUM(A1:A5), SUM(B1:B5))
Think about it: Why might you combine functions?
Show answer
To calculate more complex values automatically, such as the average of two totals.
9. Common Function Errors
- #VALUE! – text in a numeric formula
- #NAME? – function name typed incorrectly
- #DIV/0! – division by zero
- #REF! – a referenced cell was deleted
Quick test: Why does =SUMM(A1:A5) cause #NAME?
Because “SUMM” is not a valid function name.
10. Practical Activity
- Create a table with 12 months and Sales values.
- Use SUM to calculate the annual total.
- Use AVERAGE to calculate the monthly average.
- Use MAX to find the highest month.
- Use MIN to find the lowest month.
- Use COUNT to count numeric entries.
- Use COUNTA to count all entries.
- Use COUNTBLANK to find missing data.
1. What is a function?
A function is a predefined formula that follows a specific structure:
=FUNCTION_NAME(arguments)
Examples:
- =SUM(A1:A5)
- =AVERAGE(B1:B10)
- =MAX(C2:C20)
2. SUM Function
Adds all numbers in a selected range.
Example: =SUM(A1:A10)
Uses:
- Total sales
- Total expenses
- Total hours worked
3. AVERAGE Function
Calculates the mean (average) of values in a range.
Example: =AVERAGE(B1:B12)
4. MIN and MAX
- =MIN(range) – finds the smallest value.
- =MAX(range) – finds the largest value.
Examples:
- =MIN(C1:C10)
- =MAX(C1:C10)
5. COUNT, COUNTA and COUNTBLANK
- COUNT – counts cells that contain numbers.
- COUNTA – counts any non-empty cells.
- COUNTBLANK – counts empty cells.
Examples:
- =COUNT(A1:A20)
- =COUNTA(A1:A20)
- =COUNTBLANK(A1:A20)
6. AutoSum
AutoSum automatically inserts a SUM formula.
How to use:
- Select the cell below a column of numbers.
- Click AutoSum (Σ).
- Press Enter.
7. Function Arguments
The information inside the brackets is called an argument.
Examples:
- =SUM(A1:A10) – one argument (a range).
- =SUM(A1, B1, C1) – multiple arguments.
8. Combining Functions
You can use functions inside other functions.
Example (average of two sums):
=AVERAGE(SUM(A1:A5), SUM(B1:B5))
9. Common Function Errors
- #VALUE! – text where a number is required.
- #NAME? – function name typed incorrectly.
- #DIV/0! – division by zero.
- #REF! – invalid or deleted cell reference.
10. Practical Activity
- Create a table with 12 months and Sales figures.
- Use SUM to calculate the annual total.
- Use AVERAGE to calculate the monthly average.
- Use MAX to find the highest month.
- Use MIN to find the lowest month.
- Use COUNT to count numeric entries.
- Use COUNTA to count all entries.
- Use COUNTBLANK to identify missing data.