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

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(logicaltest, valueiftrue, valueif_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(lookupvalue, tablearray, colindex, [rangelookup])`	Search a column for a key and return a value from the same row.
HLOOKUP	`=HLOOKUP(lookupvalue, tablearray, rowindex, [rangelookup])`	Same as VLOOKUP but searches across a row.
LOOKUP	`=LOOKUP(lookupvalue, lookupvector, 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(startdate, enddate, "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, numdigits)` / `=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.	–

Support e-Consult Kenya

Your generous donation helps us continue providing free Cambridge IGCSE & A-Level resources, past papers, syllabus notes, revision questions, and high-quality online tutoring to students across Kenya.