Year 10 · ICT · Summer 1

Spreadsheets · Lesson 3 of 5

Named ranges and formatting

Practical20.1b Named ranges and functions20.3 Display, format and print

Year 10 · ICT · Spreadsheets · Lesson 3 of 5

You will learn to

  • Create a named range and use it in formulae.
  • Format numbers as currency and percentages, with a set number of decimal places.
  • Explain that a number format changes how a value looks, not the value itself.
  • Apply conditional formatting with a rule, such as 'over budget'.

Year 10 · ICT · Spreadsheets · Lesson 3 of 5

Success criteria

You have got it when you can say:

  • I can name the price list and use the name in a VLOOKUP.
  • I can format a column as euros with 2 decimal places.
  • I can make cells turn red by themselves when they go over budget.

Year 10 · ICT · Spreadsheets · Lesson 3 of 5

Retrieval starter

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

  1. Last lesson

    $B$1 in a copied formula…

    A Stays as B1B Shifts to B2C Becomes an error

    A. Stays as B1

  2. Last unit

    A widow or orphan is…

    A A spelling mistakeB A single line of a paragraph left alone at the top or bottom of a pageC A blank page

    B. A single line of a paragraph left alone at the top or bottom of a page

  3. Earlier this year

    An intranet is…

    A A private network inside an organisationB The public internetC A type of cable

    A. A private network inside an organisation

Year 10 · ICT · Spreadsheets · Lesson 3 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 3 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 3 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 10 · ICT · Spreadsheets · Lesson 3 of 5

Key words (4 of 4)

Wildcard
A symbol standing for unknown characters: * for any number, ? for exactly one.
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.

20.1b Named ranges and functions · The explanation · step 1 of 13 · 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 13 · 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 13 · 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 13 · 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 13 · 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 13 · 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 13 · 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 13 · 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 13 · 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 13 · 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 13 · 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 · 9 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
  • Show formulae, not only values
  • Format cells: text, shading, borders, alignment
  • Number formats
  • Conditional formatting
  • Page layout for printing
  • How spreadsheet marks are lost

Open the lesson page

Year 10 · ICT · Spreadsheets · Lesson 3 of 5

Quick quiz · 1 of 3

VLOOKUP's last argument FALSE means…

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

C. An exact match is required

Use FALSE for codes and names.

Year 10 · ICT · Spreadsheets · Lesson 3 of 5

Quick quiz · 2 of 3

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

  1. A Cells that are 50 or more
  2. B All the cells
  3. C Empty cells

A. Cells that are 50 or more

The condition goes in quotes.

Year 10 · ICT · Spreadsheets · Lesson 3 of 5

Quick quiz · 3 of 3

A pie chart is for…

  1. A Comparing 30 categories
  2. B Parts of a whole
  3. C Change over time

B. Parts of a whole

Few slices, adding up to the total.

Year 10 · ICT · Spreadsheets · Lesson 3 of 5

Exit ticket

Answer on paper before you go. Then check.

  1. A line chart is for…

    A Parts of a wholeB Unrelated categoriesC Change over time

    C. Change over time Temperature by month, sales by year.

  2. A legend shows…

    A Which colour is which data seriesB The chart titleC The axis scale

    A. Which colour is which data series Needed when there is more than one series.