Be able to use arithmetic operators in formulae including add, subtract, multiply, divide, indices

Resources → Subject Notes → Information Communication Technology ICT → Lesson Plan

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)

  1. Parentheses – evaluate everything inside first.
  2. Exponents – the ^ operator.
  3. Multiplication and Division – left‑to‑right.
  4. 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

  1. Start with =.
  2. Enter the first operand (number, cell reference, named range, or function).
  3. Enter the operator (+, -, *, /, ^).
  4. Enter the second operand.
  5. Use parentheses ( ) to group any part that must be evaluated first.
  6. 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)

  1. 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
  2. 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)
  3. Using cells E3 (base) and F3 (exponent), write a formula that computes \(E3^{F3}\).
    Answer: =E3 ^ F3
  4. 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)
  5. 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. –

Create an account or Login to take a Quiz

97 views
0 improvement suggestions

Log in to suggest improvements to this note.