Spreadsheet formulas
Write real formulas on a marks sheet, the way you would in Excel. Use SUM, AVERAGE, MAX, MIN and COUNT, then copy a formula and see why some cells need $ signs.
E2 is empty. Type a formula that starts with =, or tap a function above.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Maths | Science | ICT | Total | % |
| 2 | Amal | 78 | 65 | 82 | ||
| 3 | Nimali | 91 | 74 | 88 | ||
| 4 | Kasun | 56 | ab | 69 | ||
| 5 | Fathima | 84 | 90 | 95 | ||
| 6 | Tharindu | 67 | 58 | 73 | ||
| 7 | ||||||
| 8 | Highest | |||||
| 9 | Lowest | |||||
| 10 | Average | |||||
| 11 | Count | |||||
| 12 | ||||||
| 13 | Full marks | 300 | ||||
| 14 | ||||||
| 15 | ||||||
| 16 | ||||||
| 17 |
Grey cells are locked. The white cells are yours.
Try these
0 of 7 done
In E2, add up Amal’s three marks with SUM.
Show a hint
A range is the first cell, a colon, then the last cell.
=SUM(B2:D2)
Select E2 and press Copy down, so every student gets a total.
Relative cell reference · සාපේක්ෂ කෝෂ යොමුව
In B8 find the highest Maths mark with MAX. In B9 find the lowest with MIN.
In B10 find the Maths average with AVERAGE. In B11 count the marks with COUNT.
Copy B8, B9, B10 and B11 across to Science and ICT. Why is the Science count only 4?
In F2 work out Amal’s percentage: total ÷ full marks × 100. The full marks are in B13.
Copy F2 down. If the copies break, lock the full marks cell with $ signs and copy it again.
Absolute cell reference · නිරපේක්ෂ කෝෂ යොමුව
What is a formula?
A formula tells the spreadsheet to work something out. It always starts with =. It uses cell names such as B2 instead of the numbers themselves, so the answer changes by itself when a mark changes.
A formula is worked out in a fixed order: brackets first, then ^, then * and /, then + and -. So =8/2*3-2^3+5 gives 9.
The five functions
=SUM(B2:D2)adds the numbers.=AVERAGE(B2:B6)adds the numbers and divides by how many there are.=MAX(B2:B6)gives the largest number.=MIN(B2:B6)gives the smallest number.=COUNT(B2:B6)counts the cells that hold a number.
B2:D2 is a range: every cell from B2 to D2. All five functions leave out cells that are empty or hold text. That is why the count for Science is 4 and not 5: Kasun was absent, and ab is text.
Relative and absolute cell references
When you copy a formula, its cell names move with it. Copy =SUM(B2:D2) one row down and it becomes =SUM(B3:D3). This is a relative cell reference, and most of the time it is exactly what you want.
Sometimes one cell must stay where it is. Every percentage divides by the full marks in B13. Copy =E2/B13*100 down and the next row divides by B14, which is empty, so it shows #DIV/0!. Put a $ in front of the column letter and the row number, =E2/$B$13*100, and that cell no longer moves. This is an absolute cell reference.
Error values
#DIV/0!: the formula divides by zero or by an empty cell.#VALUE!: the formula does arithmetic on text.#NAME?: a function name is spelt wrong.#REF!: the formula points at a cell that is not on the sheet.
Key terms
- Spreadsheet
- පැතුරුම්පත
- Cell
- කෝෂය
- Cell range
- කෝෂ පරාසය
- Formula
- සූත්රය
- Function
- ශ්රිතය
- Formula bar
- සූත්ර තීරුව
- Relative cell reference
- සාපේක්ෂ කෝෂ යොමුව
- Absolute cell reference
- නිරපේක්ෂ කෝෂ යොමුව
More to try
- Work out Kasun’s total with
=B4+C4+D4instead of SUM. Why does it show#VALUE!? - Find the gap between the best and worst Maths marks with
=MAX(B2:B6)-MIN(B2:B6). - Spell a function wrong on purpose, such as
=SUMM(B2:D2), and read the error. - Turn on Show formulas after copying, and compare each row with the one above it.
Ready for exam questions on spreadsheets? Try the Grade 10 ICT past papers.