Year 10 · ICT · Summer 1
Spreadsheets · Lesson 1 of 5
Formulas and references
Practical20.1a Cells, formulae and references
Year 10 · ICT · Spreadsheets · Lesson 1 of 5
You will learn to
- Write formulae with + − * / ^ and brackets, in the right order of operations.
- Replicate formulae using relative references.
- Use absolute and mixed references so that a cell stays fixed when a formula is copied.
- Import data from a .csv file into a spreadsheet.
Year 10 · ICT · Spreadsheets · Lesson 1 of 5
Success criteria
You have got it when you can say:
- I can explain why =(C3+C4)/2 and =C3+C4/2 give different answers.
- I can fill a formula down and predict each new reference.
- I can fix a broken fill-down by adding $ signs.
Year 10 · ICT · Spreadsheets · Lesson 1 of 5
Retrieval starter
Questions from before. Write your answers, then show them.
- Last unit
Extreme test data is…
A Typical dataB Outside the rangeC At the edges of the allowed range
C. At the edges of the allowed range
- Last unit
Questionnaires are used in…
A ImplementationB EvaluationC Analysis
C. Analysis
- Earlier this year
A .pdf is used because…
A It looks the same on every computerB Anyone can edit itC It is the smallest file
A. It looks the same on every computer
Year 10 · ICT · Spreadsheets · Lesson 1 of 5
Key words (1 of 3)
- Cell
- One box in the grid, where a column and a row meet.
- Cell reference
- The address of a cell: column letter then row number, such as C3.
- Range
- A block of cells written first:last, such as C3:C8.
- Formula
- A calculation in a cell that starts with =.
Year 10 · ICT · Spreadsheets · Lesson 1 of 5
Key words (2 of 3)
- Replication
- Copying a formula into other cells, usually with the fill handle.
- Relative reference
- A reference like E3 that moves when the formula is copied.
- Absolute reference
- A reference like $I$7 that stays fixed when the formula is copied.
- Mixed reference
- A reference with only the column or only the row fixed, such as $I7 or I$7.
Year 10 · ICT · Spreadsheets · Lesson 1 of 5
Key words (3 of 3)
- Hard-coded value
- A number typed into a formula instead of a cell reference; it does not update.
- Lookup
- A function that finds a value in a table and returns a matching value.
- Filter
- Hiding rows that do not meet a condition, without deleting them.
- Formulae view
- A display mode that shows the formulae in cells instead of their results.
The explanation · step 1 of 7 · part 1 of 2
What the Paper 3 spreadsheet task asks
Paper 3 is a practical exam on a computer. It lasts 2 hours 15 minutes and is worth 70 marks.
It has a spreadsheet task and a website task. You also use skills from topics 11 to 16, such as file management and proofing.
There is no internet or email. Cambridge gives you source files, such as a .csv file of data.
You prove your work with evidence: printouts or screenshots, often pasted into an evidence document.
The explanation · step 1 of 7 · part 2 of 2
What the Paper 3 spreadsheet task asks (continued)
The examiner never sees your screen. They only see your evidence. If a formula is not visible in a printout, it does not exist for them.
Put your name, centre number and candidate number in the header or footer of every printout. Work with no name cannot be marked.
The explanation · step 2 of 7 · part 1 of 4
Cells, rows, columns and ranges
A spreadsheet is a grid. Columns have letters (A, B, C). Rows have numbers (1, 2, 3).
A cell is one box in the grid. Its cell reference is the column letter then the row number, like C3.
A range is a block of cells. Write the first cell, a colon, then the last cell. C3:C8 means C3, C4, C5, C6, C7 and C8.
A range can be a block: H3:I5 is 3 rows by 2 columns, so 6 cells.
The explanation · step 2 of 7 · part 2 of 4
Cells, rows, columns and ranges (continued)
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Year 10 trip to Segovia | ||||||||
| 2 | Name | Form | Paid € | Meal | Meal € | Owes € | Status | Code | Price € |
| 3 | Ana | 10A | 46.50 | V | 6.50 | 0.00 | Paid | M | 7.50 |
| 4 | Bruno | 10B | 30.00 | M | 7.50 | 17.50 | Chase | P | 6.00 |
| 5 | Carmen | 10A | 46.00 | P | 6.00 | 0.00 | Paid | V | 6.50 |
| 6 | David | 10C | 0.00 | V | 6.50 | 46.50 | Chase | ||
| 7 | Elena | 10B | 45.00 | M | 7.50 | 2.50 | Nearly | Trip cost € | 40.00 |
| 8 | Félix | 10C | 23.50 | P | 6.00 | 22.50 | Chase | ||
| 9 | Total | 191.00 | 40.00 | 89.00 | |||||
| 10 | Average | 31.83 |
The explanation · step 2 of 7 · part 3 of 4
Cells, rows, columns and ranges (continued)
The explanation · step 2 of 7 · part 4 of 4
Cells, rows, columns and ranges (continued)
Using one sheet all the way through means you can check every answer against the same numbers.
The explanation · step 3 of 7 · part 1 of 3
Import data and change the structure
Exam tasks often start with a .csv file. CSV means comma-separated values: plain text, one record per line, with commas between the fields.
When you open or import it, check that each field lands in its own column and the first row becomes the headings.
Insert rows or columns where the task says. Formulae that point to cells that move are updated for you.
Delete rows or columns the task does not need. Merge cells to make one wide cell, for example a title across A1:G1.
The explanation · step 3 of 7 · part 2 of 3
Import data and change the structure (continued)
On a computer set to Spanish, the spreadsheet may expect ; between fields, because the comma is the decimal sign. If all the data lands in column A, use the import options and choose comma as the separator.
The explanation · step 3 of 7 · part 3 of 3
Import data and change the structure (continued)
Do not merge cells inside the data list. Merged cells stop sorting and filtering from working. Merge only a title above the data.
The explanation · step 4 of 7 · part 1 of 3
Formulae and operators
A formula is a calculation in a cell. It always starts with =.
The cell shows the result. The formula stays underneath and recalculates when a cell it uses changes.
Use cell references, not typed numbers. Then the answer stays right when the data changes.
Comparison operators give TRUE or FALSE. They are used in IF, COUNTIF and filters.
The explanation · step 4 of 7 · part 2 of 3
Formulae and operators (continued)
| Operator | Meaning | Example | Result |
|---|---|---|---|
| `+` | add | `=C3+E3` | 53.00 (46.50 + 6.50) |
| `-` | subtract | `=C3-C4` | 16.50 (46.50 − 30.00) |
| `*` | multiply | `=E4*2` | 15.00 (7.50 × 2) |
| `/` | divide | `=C9/6` | 31.83 (191.00 ÷ 6) |
| `^` | to the power of | `=2^3` | 8 (2 × 2 × 2) |
| `=` | equal to | `=B3=B5` | TRUE (10A and 10A) |
| `<>` | not equal to | `=B3<>B4` | TRUE (10A and 10B) |
| `>` `<` `>=` `<=` | greater, less, greater or equal, less or equal | `=C7>=45` | TRUE (45.00 is equal) |
The explanation · step 4 of 7 · part 3 of 3
Formulae and operators (continued)
Typing =46.5+6.5 gives the right number today, but it is a hard-coded value. If a payment changes, the answer is wrong. Examiners give no mark for it.
The explanation · 3 more steps
More on the lesson page
The lesson page has the rest of the explanation, step by step:
- Order of operations and brackets
- Relative references and replication
- Absolute and mixed references ($)
Year 10 · ICT · Spreadsheets · Lesson 1 of 5
Try it: A tiny spreadsheet
Year 10 · ICT · Spreadsheets · Lesson 1 of 5
Quick quiz · 1 of 3
=IF(B2>=50,"Pass","Fail") shows Pass when…
- A B2 is less than 50
- B B2 is text
- C B2 is 50 or more
C. B2 is 50 or more
IF(test, if true, if false).
Year 10 · ICT · Spreadsheets · Lesson 1 of 5
Quick quiz · 2 of 3
$B$1 in a copied formula…
- A Stays as B1
- B Shifts to B2
- C Becomes an error
A. Stays as B1
The $ locks the reference: absolute.
Year 10 · ICT · Spreadsheets · Lesson 1 of 5
Quick quiz · 3 of 3
VLOOKUP's last argument FALSE means…
- A Case-sensitive
- B An exact match is required
- C An approximate match
B. An exact match is required
Use FALSE for codes and names.
Year 10 · ICT · Spreadsheets · Lesson 1 of 5
Exit ticket
Answer on paper before you go. Then check.
COUNTIF(C2:C30,">=50") counts…
A All the cellsB Empty cellsC Cells that are 50 or more
C. Cells that are 50 or more The condition goes in quotes.
A pie chart is for…
A Comparing 30 categoriesB Change over timeC Parts of a whole
C. Parts of a whole Few slices, adding up to the total.