Year 10 · ICT · Summer 1

Spreadsheets · Lesson 2 of 5

Functions: SUM to VLOOKUP

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

Year 10 · ICT · Spreadsheets · Lesson 2 of 5

You will learn to

  • Explain what a function and an argument are.
  • Use SUM, AVERAGE, MAX, MIN, COUNT, COUNTIF and ROUND.
  • Use IF to return one of two results, including text.
  • Use VLOOKUP with an exact match on a price list.

Year 10 · ICT · Spreadsheets · Lesson 2 of 5

Success criteria

You have got it when you can say:

  • I can say which function answers each question on the sheet.
  • I can write =IF(D3>=50,"Pass","Fail") and explain each part.
  • I can look up a price with VLOOKUP, using FALSE for an exact match.

Year 10 · ICT · Spreadsheets · Lesson 2 of 5

Retrieval starter

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

  1. Last lesson

    VLOOKUP's last argument FALSE means…

    A An approximate matchB An exact match is requiredC Case-sensitive

    B. An exact match is required

  2. Last unit

    Parallel running means…

    A Old and new systems run together for a whileB Switching over in one goC Testing in one branch first

    A. Old and new systems run together for a while

  3. Earlier this year

    A switch…

    A Sends everything to every deviceB Sends data only to the device it is forC Connects to the ISP's line

    B. Sends data only to the device it is for

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

The maths: The mean (average)

Symbols first, then the words, then the equation. Change the numbers.

Year 10 · ICT · Spreadsheets · Lesson 2 of 5

Quick quiz · 1 of 3

A line chart is for…

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

C. Change over time

Temperature by month, sales by year.

Year 10 · ICT · Spreadsheets · Lesson 2 of 5

Quick quiz · 2 of 3

A legend shows…

  1. A The axis scale
  2. B Which colour is which data series
  3. C The chart title

B. Which colour is which data series

Needed when there is more than one series.

Year 10 · ICT · Spreadsheets · Lesson 2 of 5

Quick quiz · 3 of 3

Non-contiguous data means…

  1. A Ranges that are not next to each other
  2. B A single column
  3. C Numbers only

A. Ranges that are not next to each other

Hold Ctrl to select them.

Year 10 · ICT · Spreadsheets · Lesson 2 of 5

Exit ticket

Answer on paper before you go. Then check.

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

    A B2 is 50 or moreB B2 is less than 50C B2 is text

    A. B2 is 50 or more IF(test, if true, if false).

  2. $B$1 in a copied formula…

    A Shifts to B2B Stays as B1C Becomes an error

    B. Stays as B1 The $ locks the reference: absolute.