Year 10 · ICT · Summer 1
Spreadsheets · Lesson 5 of 5
A full past-paper spreadsheet task
Exam practice20.1c Lookup functions20.1d Decisions with IF20.3 Display, format and print
Year 10 · ICT · Spreadsheets · Lesson 5 of 5
You will learn to
- Complete a full past-paper spreadsheet task under timed conditions.
- Give clear evidence: printouts or screenshots showing values and formulae.
- Evaluate your work against the mark scheme, and record each lost mark and its cause.
Year 10 · ICT · Spreadsheets · Lesson 5 of 5
Success criteria
You have got it when you can say:
- I can finish the task in the time set, at about 2 minutes per mark.
- I can mark my work with the mark scheme, point by point.
- I can name my three most common lost marks and how to avoid them.
Year 10 · ICT · Spreadsheets · Lesson 5 of 5
Retrieval starter
Questions from before. Write your answers, then show them.
- Last lesson
COUNTIF(C2:C30,">=50") counts…
A Empty cellsB All the cellsC Cells that are 50 or more
C. Cells that are 50 or more
- Last unit
Validation checks that…
A The data is what the person meantB The spelling is rightC The data is sensible: the right type and in range
C. The data is sensible: the right type and in range
- Earlier this year
Landscape orientation is…
A Wider than it is tallB Taller than it is wideC Square
A. Wider than it is tall
Year 10 · ICT · Spreadsheets · Lesson 5 of 5
Key words (1 of 4)
- 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 5 of 5
Key words (2 of 4)
- Function
- A ready-made formula with a name, such as SUM or VLOOKUP.
- Argument
- One input to a function, written inside its brackets and separated by commas.
- Nested function
- A function used inside another function, such as ROUND(AVERAGE(...),2).
- Absolute reference
- A reference like $I$7 that stays fixed when the formula is copied.
Year 10 · ICT · Spreadsheets · Lesson 5 of 5
Key words (3 of 4)
- Named range
- A name, such as TripCost, given to a cell or range and used in formulae.
- Lookup
- A function that finds a value in a table and returns a matching value.
- Exact match
- A lookup that only accepts the identical value (FALSE in VLOOKUP).
- Conditional formatting
- Formatting that changes automatically when a cell meets a rule.
Year 10 · ICT · Spreadsheets · Lesson 5 of 5
Key words (4 of 4)
- Formulae view
- A display mode that shows the formulae in cells instead of their results.
The explanation · step 1 of 17 · part 1 of 3
Run it like the real exam
Paper 3 lasts 2 hours 15 minutes and has 70 marks. 135 ÷ 70 is about 2 minutes per mark.
Count the marks on the spreadsheet task. Set the timer from that: 30 marks means about 60 minutes.
The explanation · step 1 of 17 · part 2 of 3
Run it like the real exam (continued)
- Copy the source files into a new folder. Work only on the copies.
- Read the whole task once before you start.
- Add your name, centre number and candidate number to the header or footer first.
- Do each step in order. Tick it on the paper when it is done.
- Save with the exact file names the task gives.
The explanation · step 1 of 17 · part 3 of 3
Run it like the real exam (continued)
No internet and no help during the timed run. It must feel like the real exam.
The explanation · step 2 of 17
Mark it against the scheme
The mark scheme lists one point for each mark. The examiner only sees your evidence, never your screen.
- Give a mark only if the evidence shows it clearly.
- A formula that is cut off in the printout gets no mark.
- A typed number where a formula should be gets no mark.
- Check every cell in the range, not only the first one.
If it is not in the evidence, it did not happen.
The explanation · step 3 of 17 · part 1 of 2
The lost-marks log
Write down every lost mark. Patterns show up after two or three tasks.
| Step | Marks lost | Why | Fix next time |
|---|---|---|---|
| Formulae printout | 2 | Columns too narrow, formulae cut off | Widen columns in formulae view before printing |
| VLOOKUP copied down | 1 | No $ on the lookup range, so copies broke | Make the lookup range absolute, or name it |
| Values printout | 1 | No name in the header | Add the header before the first printout |
The explanation · step 3 of 17 · part 2 of 2
The lost-marks log (continued)
Why keep a log?
Most practical marks are lost the same way each time. A log turns a vague worry into a short checklist.
20.1c Lookup functions · The explanation · step 4 of 17 · part 1 of 4
VLOOKUP: exact match
VLOOKUP (vertical lookup) searches down the first column of a table for a value. Then it returns a value from the same row.
It has four arguments. The table shows each one.
Copied down column E, it finds the right meal price for every pupil.
20.1c Lookup functions · The explanation · step 4 of 17 · part 2 of 4
VLOOKUP: exact match (continued)
| Argument | What it means | In =VLOOKUP(D3,$H$3:$I$5,2,FALSE) |
|---|---|---|
| lookup_value | what to look for | D3 (the code V) |
| table_array | the table; its first column holds the codes | $H$3:$I$5 (absolute, so it does not move) |
| col_index_num | which column of the table to return, counting from 1 at the table's left edge | 2 (column I, Price €) |
| range_lookup | FALSE = exact match only. TRUE = approximate match | FALSE |
20.1c Lookup functions · The explanation · step 4 of 17 · part 3 of 4
VLOOKUP: exact match (continued)
Exact match (FALSE) is right for codes, names and IDs. A missing code gives #N/A. That warns you, instead of quietly giving a wrong price.
20.1c Lookup functions · The explanation · step 4 of 17 · part 4 of 4
VLOOKUP: exact match (continued)
The column number counts from the left edge of the table, not from column A. Column I is column 2 of H3:I5, not column 9.
20.1c Lookup functions · The explanation · step 7 of 17 · part 1 of 2
LOOKUP
LOOKUP searches one column (or row) and returns the value in the same position from another column (or row).
Argument 1, lookup_value: what to find. Argument 2, lookup_vector: the single column to search. Argument 3, result_vector: the column of answers, the same size.
LOOKUP always does an approximate match. The lookup vector must be sorted in ascending order. With no exact match, it uses the largest value that is smaller.
20.1c Lookup functions · The explanation · step 7 of 17 · part 2 of 2
LOOKUP (continued)
LOOKUP has no exact-only mode. A missing code X returns the price for V (the largest code before X) instead of an error. Use it only on sorted data, and only when the question asks for it.
The explanation · 12 more steps
More on the lesson page
The lesson page has the rest of the explanation, step by step:
- VLOOKUP: approximate match
- HLOOKUP
- XLOOKUP
- IF
- Nested IF and other nested functions
- Error values and what they mean
- Show formulae, not only values
- Format cells: text, shading, borders, alignment
- Number formats
- Conditional formatting
- Page layout for printing
- How spreadsheet marks are lost
Year 10 · ICT · Spreadsheets · Lesson 5 of 5
Quick quiz · 1 of 3
A pie chart is for…
- A Change over time
- B Comparing 30 categories
- C Parts of a whole
C. Parts of a whole
Few slices, adding up to the total.
Year 10 · ICT · Spreadsheets · Lesson 5 of 5
Quick quiz · 2 of 3
A line chart is for…
- A Change over time
- B Parts of a whole
- C Unrelated categories
A. Change over time
Temperature by month, sales by year.
Year 10 · ICT · Spreadsheets · Lesson 5 of 5
Quick quiz · 3 of 3
A legend shows…
- A The chart title
- B Which colour is which data series
- C The axis scale
B. Which colour is which data series
Needed when there is more than one series.
Year 10 · ICT · Spreadsheets · Lesson 5 of 5
Exit ticket
Answer on paper before you go. Then check.
Non-contiguous data means…
A Numbers onlyB Ranges that are not next to each otherC A single column
B. Ranges that are not next to each other Hold Ctrl to select them.
=IF(B2>=50,"Pass","Fail") shows Pass when…
A B2 is 50 or moreB B2 is textC B2 is less than 50
A. B2 is 50 or more IF(test, if true, if false).
Year 10 · ICT · Spreadsheets · Lesson 5 of 5
Homework and the challenge
Homework
The ICT 20 and ICT 16 quizzes.
End-of-unit challenge
End-of-unit test: a timed Paper 3 spreadsheet section.