Year 11 · ICT · Spring 1

Paper 3 practice: spreadsheets and websites · Lesson 5 of 5

A full Paper 3

Exam practice20.3 Display, format and print20.1d Decisions with IF21.3b CSS: page styles, tables and the cascade

Year 11 · ICT · Paper 3 practice: spreadsheets and websites · Lesson 5 of 5

You will learn to

  • Complete a full Paper 3 in 2 hours 15 minutes.
  • Use a timing plan with clock times for each task and 15 minutes to check.
  • Use the end-of-paper checks: every formula visible, every link tested, the stylesheet attached.
  • Use the mark scheme to mark the paper and update your tracker.

Year 11 · ICT · Paper 3 practice: spreadsheets and websites · Lesson 5 of 5

Success criteria

You have got it when you can say:

  • I can write my clock times on the paper before I start.
  • I can run the end-of-paper checklist before the time runs out.
  • I can list my lost marks by cause.

Year 11 · ICT · Paper 3 practice: spreadsheets and websites · Lesson 5 of 5

Retrieval starter

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

  1. Last lesson

    .csv is…

    A A web pageB Comma-separated values: a generic data formatC A picture

    B. Comma-separated values: a generic data format

  2. Last unit

    Non-contiguous data means…

    A Ranges that are not next to each otherB A single columnC Numbers only

    A. Ranges that are not next to each other

  3. Earlier this year

    Netiquette is…

    A A search engineB Polite behaviour onlineC A network cable

    B. Polite behaviour online

Year 11 · ICT · Paper 3 practice: spreadsheets and websites · Lesson 5 of 5

Key words (1 of 8)

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 · Paper 3 practice: spreadsheets and websites · Lesson 5 of 5

Key words (2 of 8)

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).
Relative reference
A reference like E3 that moves when the formula is copied.

Year 11 · ICT · Paper 3 practice: spreadsheets and websites · Lesson 5 of 5

Key words (3 of 8)

Absolute reference
A reference like $I$7 that stays fixed when the formula is copied.
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).

Year 11 · ICT · Paper 3 practice: spreadsheets and websites · Lesson 5 of 5

Key words (4 of 8)

Conditional formatting
Formatting that changes automatically when a cell meets a rule.
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.

Year 11 · ICT · Paper 3 practice: spreadsheets and websites · Lesson 5 of 5

Key words (5 of 8)

Tag
A word in angle brackets, such as `<p>`, that marks the start or end of an element.
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.

Year 11 · ICT · Paper 3 practice: spreadsheets and websites · Lesson 5 of 5

Key words (6 of 8)

Inline style
CSS written in a style attribute inside one tag; it affects only that element.
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.

Year 11 · ICT · Paper 3 practice: spreadsheets and websites · Lesson 5 of 5

Key words (7 of 8)

Cascade
The rules that decide which style wins when two styles set the same property.
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.

Year 11 · ICT · Paper 3 practice: spreadsheets and websites · Lesson 5 of 5

Key words (8 of 8)

Alt text
Alternative text for an image, read by screen readers and shown if the image fails.

The explanation · step 1 of 17 · part 1 of 2

Timing Paper 3

Paper 3 is 70 marks in 2 hours 15 minutes. That is just under 2 minutes a mark.

It has a spreadsheet task and a website task. Both need evidence.

  1. Read the whole paper first.
  2. Write a clock time for moving on from the first task to the second.
  3. Keep the last 15 minutes for the end-of-paper checks.

The explanation · step 1 of 17 · part 2 of 2

Timing Paper 3 (continued)

!Watch out

Do not spend the whole paper on one task. Half-finished work on both tasks usually scores more than one perfect task.

The explanation · step 2 of 17

End-of-paper checks

TaskCheck
SpreadsheetFormulae printout: every formula readable in full. Values printout: no cut-off values.
SpreadsheetChart: correct type, title, axis titles and labels as asked.
WebsiteThe stylesheet is attached with a relative path.
WebsiteEvery link opens the right page, in the right window.
WebsiteEvery image shows, at the stated size, with alt text.
BothYour name, centre number and candidate number on every printout. Files saved as asked.
★Remember

Test it, do not trust it. Click every link and look at every printout before you finish.

20.3 Display, format and print · The explanation · step 3 of 17 · part 1 of 3

Show formulae, not only values

Normally cells show values: the results. Formulae view shows what is typed in each cell.

Most spreadsheets have a show formulas option, often on the formulas tab or in the view settings.

In formulae view, widen columns until every formula is fully visible. A cut-off formula cannot be marked.

Adjust row heights and column widths, and wrap text in long headings, so nothing is hidden. Hide or show rows and columns only when the task says.

20.3 Display, format and print · The explanation · step 3 of 17 · part 2 of 3

Show formulae, not only values (continued)

E3   =VLOOKUP(D3,$H$3:$I$5,2,FALSE)
E4   =VLOOKUP(D4,$H$3:$I$5,2,FALSE)
...  (same pattern down to E8)
F3   =$I$7+E3-C3
F4   =$I$7+E4-C4
...  (same pattern down to F8)
G3   =IF(F3=0,"Paid",IF(F3<=10,"Nearly","Chase"))
...  (same pattern down to G8)
C9   =SUM(C3:C8)
E9   =SUM(E3:E8)
F9   =SUM(F3:F8)
C10  =AVERAGE(C3:C8)

20.3 Display, format and print · The explanation · step 3 of 17 · part 3 of 3

Show formulae, not only values (continued)

?Why?

In formulae view numbers lose their formatting, so 46.50 shows as 46.5. That is normal. The examiner wants to see the formulae, not the formatting.

20.3 Display, format and print · The explanation · step 4 of 17 · part 1 of 2

Format cells: text, shading, borders, alignment

Typical tasks: make headings bold or italic, add shading (a fill colour), add borders, and set alignment.

Align text to the left and numbers to the right, so the decimal points line up.

Merge and centre a title across the top of the data, for example A1:G1.

Apply exactly what is asked, to exactly the cells named.

20.3 Display, format and print · The explanation · step 4 of 17 · part 2 of 2

Format cells: text, shading, borders, alignment (continued)

?Why?

Consistent formatting is a house style (topic 14). Examiners check that every cell in the stated range has the format, not only the first one.

20.3 Display, format and print · The explanation · step 5 of 17 · part 1 of 2

Number formats

A number format changes how a value looks. It does not change the value.

Decimal places: 31.8333 shown to 2 decimal places is 31.83. Currency adds a symbol: €31.83, or 31,83 € in Spanish style.

Percentage: the value is shown multiplied by 100, with a % sign. 0.25 shows as 25%.

Use the symbol asked for: euro (€), pound (£) or dollar ($). The $ in a currency format has nothing to do with the $ in an absolute reference.

20.3 Display, format and print · The explanation · step 5 of 17 · part 2 of 2

Number formats (continued)

e.g.Example
Share of the cost Bruno has paid: =C4/($I$7+E4) → 30.00 ÷ (40.00 + 7.50) = 30.00 ÷ 47.50 = 0.6315… Percentage format, 0 decimal places → 63% Percentage format, 1 decimal place → 63.2%
!Watch out

Do not multiply by 100 and also apply the percentage format. You would get 6315.8%. The format does the × 100 for you.

20.3 Display, format and print · The explanation · step 8 of 17 · part 1 of 2

How spreadsheet marks are lost

Most lost marks come from the evidence, not from wrong thinking.

Before each printout, ask: is everything visible, is it on the right number of pages, and is my name on it?

20.3 Display, format and print · The explanation · step 8 of 17 · part 2 of 2

How spreadsheet marks are lost (continued)

MistakeHow to avoid it
Formula cut off in the printoutwiden columns in formulae view
No row and column headings on a formulae printoutswitch them on in page setup
Typed number instead of a formula or cell referencealways point to the cell that holds the value
Missing $, so copied formulae breakcheck the last cell you copied into
Wrong range (includes a heading or total)click the formula and check the coloured outline
Sorting one column on its ownselect the whole list before sorting
Named range spelt differently from the questioncopy the name exactly
No name, centre number or candidate numberheader or footer on every printout

The explanation · 11 more steps

More on the lesson page

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

  • Conditional formatting
  • Page layout for printing
  • IF
  • Nested IF and other nested functions
  • Error values and what they mean
  • 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 · Paper 3 practice: spreadsheets and websites · Lesson 5 of 5

Quick quiz · 1 of 3

=IF(B2>=50,"Pass","Fail") shows Pass when…

  1. A B2 is 50 or more
  2. B B2 is less than 50
  3. C B2 is text

A. B2 is 50 or more

IF(test, if true, if false).

Year 11 · ICT · Paper 3 practice: spreadsheets and websites · Lesson 5 of 5

Quick quiz · 2 of 3

$B$1 in a copied formula…

  1. A Shifts to B2
  2. B Becomes an error
  3. C Stays as B1

C. Stays as B1

The $ locks the reference: absolute.

Year 11 · ICT · Paper 3 practice: spreadsheets and websites · Lesson 5 of 5

Quick quiz · 3 of 3

VLOOKUP's last argument FALSE means…

  1. A Case-sensitive
  2. B An approximate match
  3. C An exact match is required

C. An exact match is required

Use FALSE for codes and names.

Year 11 · ICT · Paper 3 practice: spreadsheets and websites · Lesson 5 of 5

Exit ticket

Answer on paper before you go. Then check.

  1. COUNTIF(C2:C30,">=50") counts…

    A Cells that are 50 or moreB Empty cellsC All the cells

    A. Cells that are 50 or more The condition goes in quotes.

  2. A stylesheet is attached with…

    A <css>B <link rel="stylesheet" href="styles.css"> in the headC <style> in the body

    B. <link rel="stylesheet" href="styles.css"> in the head The exact file name matters.

Year 11 · ICT · Paper 3 practice: spreadsheets and websites · Lesson 5 of 5

Homework and the challenge

Homework

The ICT 20 and ICT 21 quizzes.

End-of-unit challenge

A full Paper 3 under time (the spring mock, practical).