Spreadsheets – Using Arithmetic Operators, Functions & Data Tools (IGCSE ICT 0417)
1. Why Formulae Matter
- Turn a spreadsheet into a calculator – every calculation starts with
=.
- Formulae can contain:
- Numbers or text literals
- Cell references (relative, absolute, mixed)
- Arithmetic operators
- Built‑in functions
- Parentheses to control the order of evaluation
2. The Five Basic Arithmetic Operators
| Operator |
Spreadsheet Symbol |
Mathematical Equivalent |
Example (using A1 and B1) |
| Addition |
+ |
\(a+b\) |
=A1 + B1 |
| Subtraction |
- |
\(a-b\) |
=A1 - B1 |
| Multiplication |
* |
\(a\times b\) |
=A1 * B1 |
| Division |
/ |
\(\dfrac{a}{b}\) |
=A1 / B1 |
| Indices (Exponents) |
^ |
\(a^{b}\) |
=A1 ^ 2 (square of A1) |
3. Order of Operations (PEMDAS)
- Parentheses – evaluate everything inside first.
- Exponents – the
^ operator.
- Multiplication and Division – left‑to‑right.
- Addition and Subtraction – left‑to‑right.
Use parentheses to override the natural order when the exam question requires a specific sequence.
4. Cell‑Reference Types
| Reference |
Notation |
Behaviour when copied |
Typical use |
| Relative |
A1 |
Both column and row shift relative to the new location. |
Most calculations that repeat across rows or columns. |
| Absolute |
$A$1 |
Neither column nor row changes. |
Fixed constants such as tax rates, conversion factors. |
| Mixed – column absolute |
$A1 |
Column stays fixed, row changes. |
Same column of data used for many rows. |
| Mixed – row absolute |
A$1 |
Row stays fixed, column changes. |
Header value referenced across several columns. |
5. Named Ranges (Optional but Useful)
- Define a name for a single cell or a range (e.g.,
TaxRate).
- How to create:
- Excel: Formulas ► Name Manager ► New
- Google Sheets: Data ► Named ranges
- Use in a formula:
=B2 * TaxRate
- Benefits: easier reading, reduces typing errors, works with absolute references automatically.
6. Core Functions Required for the IGCSE Exam
6.1 Arithmetic & Statistical Functions
| Function |
Syntax |
When to use |
| SUM |
=SUM(number1, [number2], …) |
Add a continuous range or a list of numbers. |
| AVERAGE |
=AVERAGE(range) |
Find the mean of a set of values. |
| MIN / MAX |
=MIN(range) / =MAX(range) |
Identify the smallest or largest value. |
| COUNT |
=COUNT(range) |
Count cells that contain numbers. |
6.2 Logical Functions
| Function |
Syntax |
When to use |
| IF |
=IF(logical_test, value_if_true, value_if_false) |
Perform a conditional calculation. |
| AND / OR |
=AND(condition1, condition2,…) / =OR(...) |
Combine several logical tests. |
6.3 Lookup Functions
| Function |
Syntax |
When to use |
| VLOOKUP |
=VLOOKUP(lookup_value, table_array, col_index, [range_lookup]) |
Search a column for a key and return a value from the same row. |
| HLOOKUP |
=HLOOKUP(lookup_value, table_array, row_index, [range_lookup]) |
Same as VLOOKUP but searches across a row. |
| LOOKUP |
=LOOKUP(lookup_value, lookup_vector, result_vector) |
Simple approximate match (often used for grading tables). |
6.4 Text Functions (Syllabus 20.1)
| Function |
Syntax |
Purpose |
CONCATENATE (or &) |
=CONCATENATE(text1, text2,…) or =A1 & " " & B1 |
Join two or more text strings. |
| LEFT / RIGHT / MID |
=LEFT(text, n), =RIGHT(text, n), =MID(text, start, n) |
Extract characters from a text string. |
| LEN |
=LEN(text) |
Return the number of characters. |
| TEXT |
=TEXT(value, "format_code") |
Convert a number or date to formatted text (e.g., =TEXT(A1,"£0.00")). |
6.5 Date & Time Functions (Syllabus 20.1)
| Function |
Syntax |
Purpose |
| DATE |
=DATE(year, month, day) |
Create a date from separate components. |
| TIME |
=TIME(hour, minute, second) |
Create a time value. |
| TODAY / NOW |
=TODAY() / =NOW() |
Current date, or current date‑and‑time. |
| DAY / MONTH / YEAR |
=DAY(date), =MONTH(date), =YEAR(date) |
Extract individual parts of a date. |
| HOUR / MINUTE / SECOND |
=HOUR(time), =MINUTE(time), =SECOND(time) |
Extract parts of a time value. |
| DATEDIF |
=DATEDIF(start_date, end_date, "unit") |
Calculate the difference between two dates (e.g., "Y" for years). |
6.6 Rounding & Integer Functions (Syllabus 20.1)
| Function |
Syntax |
Purpose |
| INT |
=INT(number) |
Return the integer part (rounds down toward zero). |
| ROUND |
=ROUND(number, num_digits) |
Round to a specified number of decimal places. |
| ROUNDUP / ROUNDDOWN |
=ROUNDUP(number, num_digits) / =ROUNDDOWN(number, num_digits) |
Force rounding up or down. |
| CEILING / FLOOR |
=CEILING(number, significance) / =FLOOR(number, significance) |
Round to the nearest multiple of a given significance. |
7. Common Spreadsheet Errors and How to Fix Them
| Error Code |
Meaning |
Typical Cause |
Quick Fix |
| #DIV/0! |
Division by zero |
Denominator is 0 or blank |
Check divisor; use =IF(B1=0, "", A1/B1) |
| #VALUE! |
Wrong data type |
Text used in a numeric operation |
Convert with =VALUE(A1) or clean the data. |
| #REF! |
Invalid cell reference |
Referenced cell/row/column has been deleted |
Repair the reference or use absolute/mixed references. |
| #NAME? |
Unrecognised text |
Miss‑spelled function name or undefined named range |
Check spelling; define the named range correctly. |
| #N/A |
Value not available |
Lookup function cannot find the key |
Verify the lookup table; wrap with =IFERROR(..., "‑"). |
8. Manipulating Data (Syllabus 20.2)
- Sorting – Data ► Sort. Choose column(s), set ascending/descending.
- Filtering – Data ► Filter. Use drop‑down arrows to show rows that meet criteria (e.g., > 100).
- Searching – Ctrl+F or Find & Replace. Wild‑cards:
* – any number of characters? – exactly one character
9. Presenting Data (Syllabus 20.3)
- Number formatting – Currency, Percentage, Fixed decimal places, Accounting.
- Conditional formatting – Home ► Conditional Formatting.
- Example: Highlight cells > 100 in red:
Format cells if > 100 → Fill red.
- Cell styles & borders – Use built‑in styles for headings, totals, subtotals.
- Page layout for printing
- Orientation (Portrait / Landscape)
- Scaling (Fit to 1 page wide)
- Headers / Footers – page numbers, file name, date.
10. Building a Formula – Step‑by‑Step
- Start with
=.
- Enter the first operand (number, cell reference, named range, or function).
- Enter the operator (
+, -, *, /, ^).
- Enter the second operand.
- Use parentheses
( ) to group any part that must be evaluated first.
- Press Enter. The result appears; the formula is visible in the formula bar.
11. Example Scenarios (Exam‑style)
| Task |
Formula |
Explanation |
| Sum of a column (A2:A10) |
=SUM(A2:A10) |
Uses the SUM function – faster and less error‑prone than writing many + signs. |
VAT‑inclusive price (price in B2, VAT rate in $VAT$) |
=B2 * (1 + $VAT$) |
Multiplication with an absolute (named) reference. |
| Highest test mark in C2:C15 |
=MAX(C2:C15) |
Returns the largest numeric value. |
| Apply a 10 % discount only if the total exceeds £200 |
=IF(SUM(D2:D6) > 200, SUM(D2:D6) * 0.9, SUM(D2:D6)) |
Conditional calculation with IF. |
| Lookup a product price (code in A2, table B2:C20, column B = code, column C = price) |
=VLOOKUP(A2, $B$2:$C$20, 2, FALSE) |
Exact‑match lookup; absolute range prevents shift when copied. |
| Round a total in E5 to the nearest whole pound |
=ROUND(E5, 0) |
Zero decimal places = nearest integer. |
| Convert a numeric grade (in F2) to a text label (e.g., “Pass”/“Fail”) |
=IF(F2 >= 40, "Pass", "Fail") |
Uses IF to return text based on a logical test. |
12. Practice Questions (with Reflection)
-
In cells
A1 and B1 you have the numbers 12 and 4. Write a formula that returns the result of \(12 \div 4 + 3\).
Answer: =A1 / B1 + 3
-
Cell
C2 contains the price of an item (£25). Cell D2 contains the VAT rate (20%). Write a formula to calculate the price including VAT.
Answer: =C2 * (1 + D2)
-
Using cells
E3 (base) and F3 (exponent), write a formula that computes \(E3^{F3}\).
Answer: =E3 ^ F3
-
Calculate the total cost of 8 items where the unit price is in
G5 and a discount of 15 % is stored in H5. Show the formula.
Answer: =8 * G5 * (1 - H5)
-
Explain why the formula
=A1 ^ B1 * C1 might give a different result from =A1 ^ (B1 * C1) when A1=2, B1=3, C1=2.
Answer: In the first formula the exponent is evaluated first (\(2^{3}=8\)) and then multiplied by 2, giving 16. In the second formula the exponent is \((3 \times 2)=6\), so the result is \(2^{6}=64\). Parentheses change the order of evaluation.
Reflection Prompt (AO3)
For each question above, write a short paragraph (2‑3 sentences) answering:
- Why you chose a direct arithmetic formula rather than a function (or vice‑versa).
- What possible errors could arise (e.g., division by zero, wrong cell reference) and how you would check for them.
13. Quick Checklist for the Exam
- Start every formula with
=.
- Use the correct operator symbols (
+, -, *, /, ^).
- Apply PEMDAS, or add parentheses to force the required order.
- Choose the appropriate reference type (relative, absolute, mixed).
- Prefer built‑in functions (SUM, AVERAGE, IF, VLOOKUP, etc.) when the task asks for them.
- Check for common error values (#DIV/0!, #VALUE!, #REF!, #NAME?, #N/A).
- Format numbers correctly (currency, percentage, decimal places) and apply conditional formatting if required.
- Verify print layout (orientation, scaling, headers/footers) when a hard‑copy is part of the task.
14. Quick Review – How the Notes Match the 0417 Syllabus
| Syllabus Requirement (Section) |
What the notes cover well |
Where the notes needed expansion |
Actionable fix |
| 20.1 Create a data model – formulae, functions, order of operations, cell‑referencing |
All arithmetic operators, PEMDAS, full table of reference types, named‑range intro, extensive function list. |
Missing integer, rounding, TEXT, DATE/TIME functions. |
Added sections 6.5‑6.6 covering rounding, text, and date/time functions. |
| 20.2 Manipulate data – sort, filter, search |
Clear bullet points on sorting, filtering, searching with wild‑cards. |
None – fully covered. |
– |
| 20.3 Present data – formatting, conditional formatting, printing |
Number formatting, conditional formatting, cell styles, page‑layout checklist. |
Could emphasise percentage and accounting formats. |
Added examples of currency and percentage formatting in the checklist. |
| 20.4 Use data tools – named ranges, error checking |
Named ranges explained; common error table with fixes. |
None. |
– |