Year 11 · ICT · Summer 1

Final revision and the exams · Lesson 4 of 5

Last Paper 3 practice

Exam practice20.1c Lookup functions20.3 Display, format and print21.3b CSS: page styles, tables and the cascade

Year 11 · ICT · Final revision and the exams · Lesson 4 of 5

You will learn to

  • Complete a Paper 3 practice under time, using your timing plan.
  • Use the end-of-paper checks: formulae visible, links tested, stylesheet attached.
  • Correct every lost mark using the mark scheme.

Year 11 · ICT · Final revision and the exams · Lesson 4 of 5

Success criteria

You have got it when you can say:

  • I can finish both tasks within the time.
  • I can show a formulae printout where every formula is readable.
  • I can fix every lost mark in the corrections time.

Year 11 · ICT · Final revision and the exams · Lesson 4 of 5

Retrieval starter

Questions from before. Write your answers, then show them.

  1. Last lesson

    Zip compression is…

    A LosslessB LossyC Neither

    A. Lossless

  2. Last unit

    Bcc is used so that…

    A The email is biggerB Recipients cannot see each other's addressesC It arrives faster

    B. Recipients cannot see each other's addresses

  3. Earlier this year

    A merge field looks like…

    A «FirstName»B <p>C =SUM(A1)

    A. «FirstName»

Year 11 · ICT · Final revision and the exams · Lesson 4 of 5

Key words (1 of 7)

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 11 · ICT · Final revision and the exams · Lesson 4 of 5

Key words (2 of 7)

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.
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.

Year 11 · ICT · Final revision and the exams · Lesson 4 of 5

Key words (3 of 7)

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 11 · ICT · Final revision and the exams · Lesson 4 of 5

Key words (4 of 7)

Formulae view
A display mode that shows the formulae in cells instead of their results.
HTML
HyperText Markup Language: the language of the content layer of a web page.
CSS
Cascading Style Sheets: the language of the presentation layer, which sets how a page looks.
Tag
A word in angle brackets, such as `<p>`, that marks the start or end of an element.

Year 11 · ICT · Final revision and the exams · Lesson 4 of 5

Key words (5 of 7)

Attribute
Extra information inside an opening tag, written name="value", such as src or alt.
Head
The part of a page holding information about it: title, meta tags, stylesheet links.
External stylesheet
A separate .css file attached to pages with the link element.
Inline style
CSS written in a style attribute inside one tag; it affects only that element.

Year 11 · ICT · Final revision and the exams · Lesson 4 of 5

Key words (6 of 7)

Class
A label given to elements with the class attribute so CSS can style them as a group.
Selector
The part of a CSS rule that says which elements it styles, such as h1 or .note.
Generic font family
A fallback type of font (serif, sans-serif, monospace) used if named fonts are missing.
Cascade
The rules that decide which style wins when two styles set the same property.

Year 11 · ICT · Final revision and the exams · Lesson 4 of 5

Key words (7 of 7)

Relative path
A file location described from the current file's folder, such as images/logo.png.
Absolute path
A full file location from the very start, such as a full URL or C:/...
Padding
The space inside an element or cell between its border and its content.
Alt text
Alternative text for an image, read by screen readers and shown if the image fails.

20.1c Lookup functions · The explanation · step 1 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 1 of 17 · part 2 of 4

VLOOKUP: exact match (continued)

ArgumentWhat it meansIn =VLOOKUP(D3,$H$3:$I$5,2,FALSE)
lookup_valuewhat to look forD3 (the code V)
table_arraythe table; its first column holds the codes$H$3:$I$5 (absolute, so it does not move)
col_index_numwhich column of the table to return, counting from 1 at the table's left edge2 (column I, Price €)
range_lookupFALSE = exact match only. TRUE = approximate matchFALSE

20.1c Lookup functions · The explanation · step 1 of 17 · part 3 of 4

VLOOKUP: exact match (continued)

e.g.Example
E3: =VLOOKUP(D3,$H$3:$I$5,2,FALSE) Step 1: D3 holds V. Step 2: search down H3:H5 (M, P, V). V is in row 5. Step 3: move across to column 2 of the table, which is column I: 6.50. Result: 6.50. Copied to E4: D4 holds M → 7.50.
?Why?

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 1 of 17 · part 4 of 4

VLOOKUP: exact match (continued)

!Watch out

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 2 of 17 · part 1 of 4

VLOOKUP: approximate match

With TRUE, or with the 4th argument left out, VLOOKUP does an approximate match. It finds the largest value that is less than or equal to the lookup value.

The first column must be sorted in ascending order (smallest first). If not, the result can be wrong with no error shown.

Use it for bands: ticket prices by age, grades by mark, discounts by quantity.

20.1c Lookup functions · The explanation · step 2 of 17 · part 2 of 4

VLOOKUP: approximate match (continued)

KL
2Age fromTicket €
300.00
464.00
5188.00

20.1c Lookup functions · The explanation · step 2 of 17 · part 3 of 4

VLOOKUP: approximate match (continued)

e.g.Example
Entry tickets for the Alcázar of Segovia by age (sample prices): =VLOOKUP(15,$K$3:$L$5,2,TRUE) 15 is not in K3:K5. The largest value that is 15 or less is 6 (row 4). Column 2 of row 4 → 4.00 =VLOOKUP(18,$K$3:$L$5,2,TRUE) → 18 is found exactly → 8.00 =VLOOKUP(4,$K$3:$L$5,2,TRUE) → the largest value that is 4 or less is 0 → 0.00

20.1c Lookup functions · The explanation · step 2 of 17 · part 4 of 4

VLOOKUP: approximate match (continued)

!Watch out

Leaving out the 4th argument means TRUE. For codes, always type FALSE. Otherwise a missing code can return a wrong row instead of #N/A.

20.1c Lookup functions · The explanation · step 3 of 17 · part 1 of 3

HLOOKUP

HLOOKUP (horizontal lookup) is VLOOKUP turned on its side. It searches across the top row of a table, then goes down.

Argument 1, lookup_value: what to find (B4, the form 10B).

Argument 2, table_array: the table, with codes in its top row ($H$12:$J$13).

Argument 3, row_index_num: which row of the table to return, counting from 1 at the top (2). Argument 4, range_lookup: FALSE for an exact match.

20.1c Lookup functions · The explanation · step 3 of 17 · part 2 of 3

HLOOKUP (continued)

HIJ
1210A10B10C
13Ms RuizMr LeeMs Ortega
e.g.Example
=HLOOKUP(B4,$H$12:$J$13,2,FALSE) Step 1: B4 holds 10B. Step 2: search across H12:J12 (10A, 10B, 10C). 10B is in column I. Step 3: go down to row 2 of the table, which is row 13: Mr Lee.

20.1c Lookup functions · The explanation · step 3 of 17 · part 3 of 3

HLOOKUP (continued)

?Why?

Use HLOOKUP when the codes run across a row. Use VLOOKUP when they run down a column. Look at the table in the question before choosing.

The explanation · 14 more steps

More on the lesson page

The lesson page has the rest of the explanation, step by step:

  • LOOKUP
  • XLOOKUP
  • Show formulae, not only values
  • Format cells: text, shading, borders, alignment
  • Number formats
  • Conditional formatting
  • Page layout for printing
  • How spreadsheet marks are lost
  • A full stylesheet for h1, h2, h3, p and li
  • Table borders: thickness, colour, collapse, invisible
  • Cells: padding, spacing, size, colour and alignment
  • The cascade: which style wins
  • How website marks are lost
  • Giving evidence

Open the lesson page

Year 11 · ICT · Final revision and the exams · Lesson 4 of 5

Quick quiz · 1 of 3

Bluetooth is best for…

  1. A Linking two schools
  2. B A whole building's laptops
  3. C A headset a metre away

C. A headset a metre away

Short range, low power, two devices.

Year 11 · ICT · Final revision and the exams · Lesson 4 of 5

Quick quiz · 2 of 3

RSI is reduced by…

  1. A A bigger hard disk
  2. B A brighter screen
  3. C Breaks and an ergonomic keyboard

C. Breaks and an ergonomic keyboard

Repetitive strain injury comes from repeated movement.

Year 11 · ICT · Final revision and the exams · Lesson 4 of 5

Quick quiz · 3 of 3

Trailing cables are a…

  1. A Health issue
  2. B Safety hazard
  3. C Security risk

B. Safety hazard

Safety is accidents (tripping, fire, shock). Health is the body (eyes, back, wrists).

Year 11 · ICT · Final revision and the exams · Lesson 4 of 5

Exit ticket

Answer on paper before you go. Then check.

  1. Job sharing means…

    A Working nightsB Two people share one full-time jobC Working from home

    B. Two people share one full-time job A working pattern made easier by IT.

  2. A microprocessor-controlled washing machine…

    A Frees the owner's timeB Needs someone to watch itC Cannot be programmed

    A. Frees the owner's time Set it and leave. The trade-off is less manual skill and reliance on the device.