20.1

Topic 20

Create a data model

  • IGCSE I.C.T 0417
  • 2 min read
  • Syllabus 2026, 2027 and 2028

Objectives

  1. 1

    Create and edit a spreadsheet model

    • aInsert cell(s), row(s) and column(s), delete cell(s), row(s) and column(s), merge cells
    • bCreate formulae using cell references
    • cReplicate formulae using absolute and relative cell references where appropriate
    • dUse arithmetic operators in formulae including add, subtract, multiply, divide, indices
    • eUse named cells and named ranges
    • fUse functions including sum, average, maximum, minimum, integer, rounding, counting, LOOKUP, VLOOKUP, HLOOKUP, XLOOKUP, IF
    • gUse external data sources within functions
    • hUse nested functions
  2. 2

    Formulae and functions

    • aThe difference between a formula and a function
  3. 3

    Order of operations

    • aThe order in which mathematical operations are performed including the use brackets to make sure that formulae work
  4. 4

    Cell referencing

    • aCharacteristics and use of absolute and relative cell referencing

Cambridge IGCSE ICT 0417 syllabus 2026-2028, 20.1 Create a data model (copied for internal use as permitted by Cambridge)

Detailed · Deep study

1Building a model

Insert and delete cells, rows and columns, and merge cells for titles. Create formulae using cell references (so results update when data changes) with the arithmetic operators + − * / ^ (indices).

FormulaFunction
meaninga calculation written by the user, using cell references and operatorsa built-in, named formula that carries out a set calculation
example=B2*C2+D2=SUM(B2:B10)

Order of operations (BIDMAS): Brackets, Indices, Division and Multiplication, Addition and Subtraction. E.g. =2+34 gives 14, but =(2+3)4 gives 20. Use brackets to make sure formulae work as intended.

2Cell referencing and names

Fig. 1Replicating formulae.
ReferenceCharacteristics and use
relative (B2)changes when the formula is replicated (copied) to other cells – used for most formulae, e.g. a total in each row
absolute ($B$6)stays the same when replicated – used for a single fixed value such as a VAT rate or exchange rate
named cell / rangea cell or range given a name (e.g. VAT, Prices) that can be used in formulae; it acts as an absolute reference and makes formulae easier to read: =D2*(1+VAT)

3Functions

FunctionExampleResult
SUM=SUM(D2:D10)adds the values
AVERAGE=AVERAGE(C2:C10)mean of the values
MAX / MIN=MAX(C2:C10)largest / smallest value
INT=INT(7.8)7 (removes the decimal part)
ROUND=ROUND(3.14159, 2)3.14
COUNT=COUNT(B2:B20)number of cells containing numbers
COUNTA=COUNTA(A2:A20)number of non-empty cells
COUNTIF=COUNTIF(C2:C20,">=50")number of cells meeting a condition
IF=IF(C2>=50,"Pass","Fail")one value if the condition is true, another if false
LOOKUP=LOOKUP(F2,A2:A10,B2:B10)finds F2 in A2:A10 and returns the matching value in B2:B10
VLOOKUP=VLOOKUP(F2,$A$2:$C$10,3,FALSE)looks down the first column for F2 and returns the value from column 3 (FALSE = exact match)
HLOOKUP=HLOOKUP(F2,$A$1:$J$3,2,FALSE)looks across the first row and returns from row 2
XLOOKUP=XLOOKUP(F2,A2:A10,C2:C10,"Not found")searches one range and returns from another; can give a value if not found
=IF(C2>=70,"Distinction",IF(C2>=50,"Pass","Fail"))
=ROUND(AVERAGE(C2:C20),1)
=VLOOKUP(A2,[Prices.xlsx]Sheet1!$A$2:$B$50,2,FALSE)
Nested functions, and a VLOOKUP using an external data source (another file).

4Exam focus

Key words: formula; function; operator; BIDMAS; relative reference; absolute reference; named range; VLOOKUP; IF; nested function.

Quick self-check

Answer in your head first, then reveal.

  1. A formula is a calculation written by the user (1); a function is a built-in named formula, e.g. SUM (1).

  2. The VAT cell must not change when the formula is copied (1) because every row uses the same rate (1).

  3. 19 (1).

Finished this subtopic? Track it on your subject page.