Year 11 · ICT · Spring 1

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

Spreadsheets under time

Exam practice20.1b Named ranges and functions20.1c Lookup functions20.1d Decisions with IF

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

You will learn to

  • Use absolute references and named ranges in formulae that are replicated.
  • Use IF, VLOOKUP and COUNTIF to solve the task, using the English function names.
  • Print the sheet showing formulae and values, with every cell fully visible.
  • Use the mark scheme to mark your spreadsheet task and log the lost marks.

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

Success criteria

You have got it when you can say:

  • I can replicate a formula and check that the absolute reference stayed fixed.
  • I can write a VLOOKUP with an exact match that returns the right value.
  • I can show a formulae printout where every formula can be read in full.

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

Retrieval starter

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

  1. Last unit

    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

  2. Last unit

    A report grouped by Form means…

    A Each form's records appear together, with a count or total per groupB The records are in random orderC Only one record shows

    A. Each form's records appear together, with a count or total per group

  3. Earlier this year

    A format check on a postcode…

    A Checks the postcode existsB Checks the pattern of letters and numbersC Checks only the length

    B. Checks the pattern of letters and numbers

Year 11 · ICT · Paper 3 practice: spreadsheets and websites · Lesson 1 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 11 · ICT · Paper 3 practice: spreadsheets and websites · Lesson 1 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 11 · ICT · Paper 3 practice: spreadsheets and websites · Lesson 1 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).
Criteria
The condition COUNTIF uses to decide which cells to count.

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

Key words (4 of 4)

Wildcard
A symbol standing for unknown characters: * for any number, ? for exactly one.
Formulae view
A display mode that shows the formulae in cells instead of their results.

20.1b Named ranges and functions · The explanation · step 1 of 15 · part 1 of 2

Named cells and named ranges

You can give a cell or a range a name, such as TripCost for I7 or MealTable for H3:I5.

A name works like an absolute reference. It does not move when the formula is copied.

Names make formulae easier to read and check. =TripCost+E3-C3 tells you what it does.

To create one, select the cell or range, type the name in the name box (left of the formula bar) and press Enter. Most spreadsheets also have a define name option.

20.1b Named ranges and functions · The explanation · step 1 of 15 · part 2 of 2

Named cells and named ranges (continued)

e.g.Example
I7 named TripCost. H3:I5 named MealTable. F3: =TripCost+E3-C3 → 40.00 + 6.50 − 46.50 = 0.00 E3: =VLOOKUP(D3,MealTable,2,FALSE) → 6.50 Copied down, both still point at I7 and H3:I5.
!Watch out

Names cannot contain spaces or start with a number. Use exactly the name the question gives. A different spelling is a different name and loses the mark.

20.1b Named ranges and functions · The explanation · step 2 of 15 · part 1 of 3

Functions, arguments and Spanish spreadsheets

A function is a ready-made formula with a name, like SUM. It goes after the = sign, and its inputs go in brackets.

Each input is an argument. Arguments are separated by commas. =ROUND(C10,1) has two arguments: C10 and 1.

A function can go inside another one. This is a nested function: =ROUND(AVERAGE(C3:C8),2) gives 31.83.

In Spanish Excel, functions have Spanish names. Arguments are separated by ; because the comma is the decimal sign. The exam uses English names, so learn those.

20.1b Named ranges and functions · The explanation · step 2 of 15 · part 2 of 3

Functions, arguments and Spanish spreadsheets (continued)

English (exam)Spanish Excel
SUMSUMA
AVERAGEPROMEDIO
MAX / MINMAX / MIN
INTENTERO
ROUNDREDONDEAR
ROUNDUPREDONDEAR.MAS
ROUNDDOWNREDONDEAR.MENOS
COUNTCONTAR
English (exam)Spanish Excel
COUNTACONTARA
COUNTIFCONTAR.SI
LOOKUPBUSCAR
VLOOKUPBUSCARV
HLOOKUPBUSCARH
XLOOKUPBUSCARX
IFSI
TRUE / FALSEVERDADERO / FALSO

20.1b Named ranges and functions · The explanation · step 2 of 15 · part 3 of 3

Functions, arguments and Spanish spreadsheets (continued)

e.g.Example
English: =VLOOKUP(D3,$H$3:$I$5,2,FALSE) Spanish Excel: =BUSCARV(D3;$H$3:$I$5;2;FALSO) English: =ROUND(31.8333,1) Spanish Excel: =REDONDEAR(31,8333;1)
!Watch out

If the exam computer runs a Spanish spreadsheet, the printout shows Spanish names. Ask the exams officer early whether the software can be set to English. In any written answer, use English names and commas.

20.1b Named ranges and functions · The explanation · step 3 of 15 · part 1 of 3

SUM, AVERAGE, MAX and MIN

Each takes one or more arguments: ranges, cells or numbers, separated by commas.

They use only numbers. They ignore empty cells and text. A 0 is a number, so it is included.

=SUM(C3:C8,E3:E8) adds two ranges: 191.00 + 40.00 = 231.00.

Try the toy: it holds a café order. Click D2, change the quantity to 5 in the formula bar and press Enter. Watch every result that depends on it update. Switch on Show formulae to see what is typed in each cell.

20.1b Named ranges and functions · The explanation · step 3 of 15 · part 2 of 3

SUM, AVERAGE, MAX and MIN (continued)

FunctionWhat it doesExampleResult
SUMadds all the numbers`=SUM(C3:C8)`191.00
AVERAGEthe mean: total ÷ how many numbers`=AVERAGE(C3:C8)`31.83
MAXthe largest number`=MAX(C3:C8)`46.50
MINthe smallest number`=MIN(C3:C8)`0.00

20.1b Named ranges and functions · The explanation · step 3 of 15 · part 3 of 3

SUM, AVERAGE, MAX and MIN (continued)

e.g.Example
=AVERAGE(C3:C8) Step 1: add 46.50 + 30.00 + 46.00 + 0.00 + 45.00 + 23.50 = 191.00 Step 2: count the numbers: 6 Step 3: 191.00 ÷ 6 = 31.8333…, shown as 31.83 with 2 decimal places
!Watch out

AVERAGE ignores a blank cell but counts a 0. If David's cell were empty instead of 0.00, the average would be 191.00 ÷ 5 = 38.20.

There is something to try for this step in the IGCSE library.

20.1b Named ranges and functions · The explanation · step 4 of 15 · part 1 of 3

INT and ROUND

INT (integer) has one argument: a number. It removes the decimal part, rounding down to a whole number.

ROUND has two arguments: the number, then how many decimal places to keep. If the next digit is 5 or more it rounds up. Below 5 it rounds down.

A negative number of places rounds to tens, hundreds and so on.

20.1b Named ranges and functions · The explanation · step 4 of 15 · part 2 of 3

INT and ROUND (continued)

Formula (C10 = 31.8333…)ResultWhy
`=INT(C10)`31the .8333 is removed
`=ROUND(C10,0)`32next digit 8 is 5 or more, so round up
`=ROUND(C10,1)`31.8next digit 3 is below 5, so round down
`=ROUND(C10,2)`31.83next digit 3 is below 5
`=ROUND(C10,-1)`30rounds to the nearest 10

20.1b Named ranges and functions · The explanation · step 4 of 15 · part 3 of 3

INT and ROUND (continued)

?Why?

ROUND changes the value stored in the cell. Formatting to 2 decimal places only changes what you see. A cell formatted to show 32 still holds 31.8333 and gives different totals later.

!Watch out

INT always goes down, even for negative numbers. =INT(-2.5) gives −3, not −2.

The explanation · 11 more steps

More on the lesson page

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

  • ROUNDUP and ROUNDDOWN
  • COUNT and COUNTA
  • COUNTIF
  • VLOOKUP: exact match
  • VLOOKUP: approximate match
  • HLOOKUP
  • LOOKUP
  • XLOOKUP
  • IF
  • Nested IF and other nested functions
  • Error values and what they mean

Open the lesson page

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

Try it: A tiny spreadsheet

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

Quick quiz · 3 of 3

VLOOKUP's last argument FALSE means…

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

A. An exact match is required

Use FALSE for codes and names.

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

Exit ticket

Answer on paper before you go. Then check.

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

    A Empty cellsB All the cellsC Cells that are 50 or more

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

  2. A stylesheet is attached with…

    A <style> in the bodyB <css>C <link rel="stylesheet" href="styles.css"> in the head

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