Skip to the page
Year 11 · ICT · Spring 1 · Paper 3 practice: spreadsheets and websites

Spreadsheets under time

Exam practicePresent ▶

What this lesson covers

20.1b Named ranges and functions

Cambridge IGCSE ICT 0417 · Topic 20: Spreadsheets · Paper 3

20.1c Lookup functions

Cambridge IGCSE ICT 0417 · Topic 20: Spreadsheets · Paper 3

20.1d Decisions with IF

Cambridge IGCSE ICT 0417 · Topic 20: Spreadsheets · Paper 3

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.

Success criteria

  • 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.
The syllabus points for these sections
  • Create and use named cells and named ranges.
  • Explain what a function and an argument are, and nest one function inside another.
  • Use SUM, AVERAGE, MAX, MIN, INT, ROUND, ROUNDUP and ROUNDDOWN.
  • Use COUNT, COUNTA and COUNTIF, including comparisons and wildcards.
  • Recognise the Spanish names of these functions and why the exam uses English ones.
  • Use VLOOKUP with an exact match and with an approximate match.
  • Use HLOOKUP on a table laid out across rows.
  • Use LOOKUP on sorted data and know its limits.
  • Use XLOOKUP, including a message when nothing is found.
  • Use IF to return one of two results.
  • Write a nested IF with three or more results, in a sensible order.
  • Recognise error values and fix the formula that caused them.

Key words

Say each word, then read what it means. Every one is in the glossary too.

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

The explanation

Read it here, or open the study page below to go one step at a time with things to try.

20.1b Named ranges and functions

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.

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.

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.

English (exam)Spanish Excel
SUMSUMA
AVERAGEPROMEDIO
MAX / MINMAX / MIN
INTENTERO
ROUNDREDONDEAR
ROUNDUPREDONDEAR.MAS
ROUNDDOWNREDONDEAR.MENOS
COUNTCONTAR
COUNTACONTARA
COUNTIFCONTAR.SI
LOOKUPBUSCAR
VLOOKUPBUSCARV
HLOOKUPBUSCARH
XLOOKUPBUSCARX
IFSI
TRUE / FALSEVERDADERO / FALSO
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.

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.

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

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.

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

ROUNDUP and ROUNDDOWN

Both take the same two arguments as ROUND: the number, then the number of decimal places.

ROUNDUP always rounds away from zero. ROUNDDOWN always rounds towards zero. The next digit does not matter.

Use ROUNDUP when you must have enough: buses, boxes, tickets. Use ROUNDDOWN when you cannot go over: how many items you can afford.

Formula (C10 = 31.8333…)Result
`=ROUNDUP(C10,1)`31.9
`=ROUNDDOWN(C10,1)`31.8
`=ROUNDUP(C10,0)`32
`=ROUNDDOWN(C10,0)`31
e.g.Example
130 pupils go on the trip. Each bus has 55 seats. 130 ÷ 55 = 2.36… =ROUND(130/55,0) → 2 buses. 2 × 55 = 110 seats, so 20 pupils are left behind. =ROUNDUP(130/55,0) → 3 buses. Everyone travels. Each pupil has €20 for souvenirs at €3.50 each: 20 ÷ 3.5 = 5.71… =ROUNDDOWN(20/3.5,0) → 5 souvenirs. A 6th would cost €21.

COUNT and COUNTA

COUNT counts the cells in a range that hold numbers.

COUNTA counts the cells that are not empty: numbers, text, anything.

Both take one or more ranges as arguments. Neither counts empty cells.

FormulaResultWhy
`=COUNT(C3:C8)`6six amounts (0.00 is a number)
`=COUNT(A3:A8)`0names are text, not numbers
`=COUNTA(A3:A8)`6six names
`=COUNTA(A3:A10)`8also counts the words Total and Average
?Why?

To count pupils, count names with COUNTA. To count how many payments were entered, count numbers with COUNT.

!Watch out

Choose the range carefully. Including a heading or the total row changes the count.

COUNTIF

COUNTIF counts the cells in a range that meet one condition.

Argument 1 is the range to check. Argument 2 is the criteria: the condition, in double quotes.

Criteria can be a value ("10A"), a comparison (">0"), or text with wildcards. * means any number of characters. ? means exactly one character.

To use a cell in the criteria, join it on with &: ">"&I7 means "greater than the value in I7".

FormulaResultCells counted
`=COUNTIF(B3:B8,"10A")`2Ana, Carmen
`=COUNTIF(F3:F8,">0")`4Bruno, David, Elena, Félix
`=COUNTIF(C3:C8,">=45")`3Ana, Carmen, Elena
`=COUNTIF(G3:G8,"Paid")`2Ana, Carmen
`=COUNTIF(A3:A8,"*a")`2Ana, Elena (names ending in a)
`=COUNTIF(F3:F8,">"&I7)`1David (46.50 is more than 40.00)
!Watch out

A comparison without quotes (>0 instead of ">0") is an error. Criteria are not case-sensitive, so "paid" also matches Paid.

20.1c Lookup functions

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.

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

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

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.

KL
2Age fromTicket €
300.00
464.00
5188.00
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
!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.

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.

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

LOOKUP

LOOKUP searches one column (or row) and returns the value in the same position from another column (or row).

Argument 1, lookup_value: what to find. Argument 2, lookup_vector: the single column to search. Argument 3, result_vector: the column of answers, the same size.

LOOKUP always does an approximate match. The lookup vector must be sorted in ascending order. With no exact match, it uses the largest value that is smaller.

e.g.Example
=LOOKUP(D4,$H$3:$H$5,$I$3:$I$5) D4 holds M. M is 1st in H3:H5 (M, P, V are in alphabetical order). The 1st value in I3:I5 is 7.50. Result: 7.50
!Watch out

LOOKUP has no exact-only mode. A missing code X returns the price for V (the largest code before X) instead of an error. Use it only on sorted data, and only when the question asks for it.

XLOOKUP

XLOOKUP is the newest lookup. It searches one column (or row) and returns from another. Unlike LOOKUP, it does an exact match by default.

Argument 1, lookup_value. Argument 2, lookup_array: where to search. Argument 3, return_array: where the answers are.

Argument 4, if_not_found, is optional: what to show when there is no match. Optional arguments 5 and 6 set the match mode and the search direction.

The answer column can be to the left of the search column. VLOOKUP cannot do that.

e.g.Example
=XLOOKUP(D8,$H$3:$H$5,$I$3:$I$5,"No meal") D8 holds P → 2nd in H3:H5 → 2nd in I3:I5 → 6.00 If D8 held X → no match → shows No meal Looking left: =XLOOKUP(46.5,F3:F8,A3:A8) → 46.50 is in F6 → A6 → David
!Watch out

XLOOKUP is only in recent spreadsheet software. Older software shows #NAME?. Check before the exam which functions your software has.

20.1d Decisions with IF

IF

IF makes a decision. It checks a condition and returns one of two results.

Argument 1, logical_test: a comparison that is TRUE or FALSE, like F3=0. Argument 2, value_if_true. Argument 3, value_if_false.

Text results go in double quotes. Numbers and cell references do not.

e.g.Example
=IF(F3=0,"Paid","Owes") F3 is 0.00 → test TRUE → Paid Copied to row 4: F4 is 17.50 → test FALSE → Owes A number result: =IF(C4>=45,0,5) → 30.00 is not 45 or more → 5 (a €5 reminder fee) The sample sheet uses a nested version of this IF. It is in the next step.
!Watch out

=IF(F3=0,Paid,Owes) without quotes gives #NAME?. The spreadsheet thinks Paid is the name of a range.

Nested IF and other nested functions

A nested IF puts a second IF inside the first, usually in the value_if_false part. Two IFs give three possible results.

The spreadsheet checks the tests from left to right and stops at the first TRUE one. So the order of the tests matters.

Every IF opens a bracket. Two IFs need two closing brackets at the end.

Any function can be nested. The inner one is worked out first and its result is passed to the outer one.

e.g.Example
G3: =IF(F3=0,"Paid",IF(F3<=10,"Nearly","Chase")) F3 = 0.00 → first test TRUE → Paid F7 = 2.50 → first test FALSE, second test 2.50 <= 10 TRUE → Nearly F4 = 17.50 → both tests FALSE → Chase Other nesting: =ROUND(AVERAGE(C3:C8),1) → AVERAGE gives 31.8333…, then ROUND gives 31.8
!Watch out

Test order matters. =IF(F3<=10,"Nearly",IF(F3=0,"Paid","Chase")) never shows Paid, because 0 is already 10 or less.

Error values and what they mean

An error value means the formula cannot give an answer. Each one points to a different problem.

In a printout, an error loses the mark for that formula, and often for cells that use it.

ErrorWhat it meansHow to fix it
`#N/A`a lookup value was not foundcheck spelling, extra spaces, FALSE, and $ on the table
`#NAME?`the spreadsheet does not recognise a wordcheck the function spelling, quotes round text, and named ranges
`#DIV/0!`dividing by zero or by an empty cellcheck the cell you divide by
`#VALUE!`wrong type of data, such as =C3+A3 (a number plus a name)use cells that hold numbers
`#REF!`a reference points to a deleted cellrewrite the formula
`####`the column is too narrow to show the numberwiden the column

Study it

Open the study page and go one step at a time.

ICT topic 20

Spreadsheets

Step by step, with exam questions and a quiz.

ICT topic 21

Website authoring

Step by step, with exam questions and a quiz.

ICT topic 12

Images

Step by step, with exam questions and a quiz.

ICT topic 16

Graphs and charts

Step by step, with exam questions and a quiz.

ICT topic 11

File management

Step by step, with exam questions and a quiz.

Topic 18 · Year 7

Data, spreadsheets and AI

Data is raw facts. Information is data with meaning. Spreadsheets calculate, databases store and search, AI learns patterns from examples.

The steps for this lesson

Try it

Check yourself

One question at a time. The reason shows after each answer.

Saved on this device.