How do built-in functions like SUM and AVERAGE save work over long formulae?
Use common spreadsheet functions (SUM, AVERAGE, MAX, MIN, COUNT) with cell ranges to summarise data
A focused answer to the O-Level Computing point on spreadsheet functions. Using SUM, AVERAGE, MAX, MIN and COUNT with cell ranges to summarise data, and the colon range notation.
Reviewed by: AI editorial process; not yet individually human-reviewed
Have a quick question? Jump to the Q&A page
Jump to a section
What this dot point is asking
SEAB wants you to use the common built-in spreadsheet functions, SUM, AVERAGE, MAX, MIN and COUNT, with a range of cells to summarise data. The central idea is that a function is a ready-made calculation: instead of writing a long formula, you name the function and give it the range of cells to work on.
The answer
What a function is
A function is a built-in operation with a name. You call it like this:
=FUNCTION(range)
It begins with =, then the function name, then brackets containing the cells it acts on. The result appears in the cell.
Cell ranges with a colon
A range is a block of cells written with a colon between the first and last cell. B2:B11 means every cell from B2 down to B11. A range can also span columns, such as A1:C3 for a rectangular block.
The five common functions
| Function | What it returns |
|---|---|
SUM |
the total of the values |
AVERAGE |
the mean of the values |
MAX |
the largest value |
MIN |
the smallest value |
COUNT |
how many cells contain a number |
=SUM(B2:B11) total of the marks
=AVERAGE(B2:B11) mean mark
=MAX(B2:B11) top mark
=MIN(B2:B11) lowest mark
=COUNT(B2:B11) number of marks entered
COUNT counts numbers, not text
A key detail: COUNT counts only cells that hold a number. Empty cells and cells holding text are ignored. This is why COUNT over a column of marks gives the number of students who actually sat the test.
Why functions beat long formulae
Functions are shorter, easier to read, and less error-prone than typing every cell reference. They also cope better when you insert or delete rows inside the range, so your summary stays correct.
Examples in context
Example 1. A teacher's mark book. A teacher uses =AVERAGE(B2:B31) for the class mean, =MAX(B2:B31) and =MIN(B2:B31) for the highest and lowest marks, and =COUNT(B2:B31) to confirm how many students were assessed. One row of functions summarises the whole class.
Example 2. A monthly budget. A household lists expenses in a column and uses =SUM for the monthly total and =MAX to spot the single biggest expense. When a new expense is added inside the range, the functions update the totals automatically.
Try this
Q1. Write a function to find the smallest value in cells D1 to D20. [2 marks]
- Cue.
=MIN(D1:D20).
Q2. State what the colon means in the range A2:A10. [1 mark]
- Cue. Every cell from A2 to A10 inclusive.
Q3. A column has values , , empty, . State what =COUNT and =SUM of that range return. [2 marks]
- Cue. COUNT returns (three numbers); SUM returns ().
Exam-style practice questions
Practice questions written in the style of SEAB exam questions on this dot point, with worked answer explainers. The year tag is the paper they imitate, not the source.
Original5 marksTest marks are stored in cells B2 to B11. Write a single function for each of: (a) the total of all marks, (b) the average mark, (c) the highest mark, (d) the number of students who sat the test.Show worked answer →
Each is a built-in function applied to the range B2:B11.
(a) =SUM(B2:B11)
(b) =AVERAGE(B2:B11)
(c) =MAX(B2:B11)
(d) =COUNT(B2:B11)
The colon means "everything from B2 through B11 inclusive". COUNT returns how many of those cells contain numbers, which here is the number of students who have a mark.
Markers reward the correct function name for each task and the range B2:B11 written with a colon.
Original4 marks(a) Explain the difference between and . (b) State one advantage of using the SUM function. (c) State what would return if A1 to A5 contained the values , empty, , , empty.Show worked answer →
(a) Both add the five cells and give the same result. =SUM(A1:A5) uses a function with a range, while =A1+A2+A3+A4+A5 lists each cell individually.
(b) SUM is shorter and easier to read, and it adjusts more easily if rows are inserted; it is also less error-prone than typing many references by hand.
(c) COUNT counts only cells containing numbers. The values , and are numbers and the two empty cells are not, so =COUNT(A1:A5) returns .
Markers reward the same result from both, an advantage of SUM such as brevity or fewer errors, and COUNT returning .
Related dot points
- Describe how a spreadsheet is organised into cells, rows and columns, and write formulae using cell references and operators
A focused answer to the O-Level Computing point on spreadsheet basics. Cells, rows and columns, cell references, writing formulae with operators, and why formulae recalculate automatically when data changes.
- Distinguish relative and absolute cell references, use the dollar sign to fix a reference, and predict how references change when a formula is copied
A focused answer to the O-Level Computing point on cell references. How relative references change when copied, how absolute references with a dollar sign stay fixed, and how to predict the result of copying a formula.
- Use the IF function for conditional results and lookup functions such as VLOOKUP to find values in a table
A focused answer to the O-Level Computing point on logical and lookup functions. Using IF for conditional results, nesting IF for grades, and VLOOKUP to find a matching value in a table.
- Choose an appropriate chart type for data, and use sorting and filtering to organise and find records
A focused answer to the O-Level Computing point on presenting data. Choosing a suitable chart (bar, line, pie), sorting records into order, and filtering to show only the rows that meet a condition.
- Use Python lists and strings, including indexing, length, looping over items, and basic operations like append and slicing
A focused answer to the O-Level Computing point on Python lists and strings. Creating lists, zero-based indexing, finding length with len(), looping over items, appending, and basic string operations and slicing.