Use spreadsheet functions (SUM, IF, VLOOKUP, COUNTIF)

Cambridge A‑Level IT 9626 – Topic 8: Spreadsheets

Learning Objectives (AO1‑AO3)

• AO1 – Knowledge & Understanding: recognise the purpose and operation of key spreadsheet features – layout, formatting, data validation, protection, referencing, functions, sorting/filtering, charts, and import/export.
• AO2 – Application: create, edit and format worksheets; use a wide range of built‑in functions; apply validation and protection; copy formulas with absolute, relative and mixed references; sort, filter and extract data; produce appropriate charts; import and export data.
• AO3 – Analysis & Evaluation: test spreadsheets with normal, extreme and abnormal data; interpret results; evaluate the suitability of functions, formatting, and visualisations for a given problem.

---

1. Layout & Structure

Set up the worksheet before entering data so that it is easy to read, print and modify.

• Page set‑up: orientation (portrait/landscape), margins, headers & footers.
• Inserting / deleting: rows, columns, worksheets (Ctrl + + / Ctrl ‑ ‑ or right‑click).
• Hiding, unhiding & grouping rows/columns to simplify large tables.
• Merging & centring cells for titles or headings.
• Naming worksheets (e.g., “Sales2024”, “Students”).

Practice Task – Simple sales register

1. Set page orientation to Landscape and choose narrow margins.
2. Enter headings in A1:E1: Invoice No., Date, Product, Qty, Unit Price.
3. Merge A1:E1, centre the text and type Monthly Sales Register.

---

2. Formatting Cells & Ranges

• Number formats: General, Number (2 dp), Currency, Percentage, Accounting, Date, Time.
• Font & colour: bold headings, colour‑code rows, use contrasting text/background.
• Conditional formatting: highlight cells that meet a condition (e.g., sales > £500 in red).
• Cell styles: apply predefined styles for headings, totals, warnings.

Example – Conditional formatting for high sales

1. Select F2:F20.
2. Home → Conditional Formatting → New Rule → “Format only cells that contain”.
3. Set Cell Value > 500, choose a red fill, click OK.

---

3. Referencing

Reference type	Notation	Behaviour when copied
Relative	A1	Adjusts both column and row.
Absolute	$A$1	Never changes.
Mixed (column absolute)	$A1	Column fixed, row changes.
Mixed (row absolute)	A$1	Row fixed, column changes.

Named ranges give a readable alternative to cell references and can be used in formulas, validation lists and charts.

Named‑range example

1. Select cell B2 (contains the tax rate 0.20).
2. Formulas → Define Name → type TaxRate → OK.
3. Use in a formula: =C2*$TaxRate. When copied down, the tax rate remains fixed.

---

4. Data Validation & Protection

• Drop‑down lists: restrict entry to a predefined set (e.g., Status = {Pending, Approved, Rejected}).
• Whole‑number, decimal, date, text‑length rules with custom input messages and error alerts.
• Worksheet protection: lock cells that contain formulas, then protect the sheet with a password.
• Workbook protection: prevent adding, deleting or renaming sheets.

Validation example – Percentage between 0 % and 100 %

1. Select D2, Data → Data Validation.
2. Allow: Decimal; Data: between; Minimum = 0; Maximum = 1.
3. Input message: “Enter a proportion (e.g., 0.75 for 75 %).”
4. Error alert: “Value must be between 0 and 1.”

---

5. Functions – Overview & Sample Syntax

The Cambridge syllabus expects familiarity with the main function groups. The table below summarises each group, a representative function, required arguments and a realistic example.

Function group	Key functions (examples)	Typical arguments	Sample formula (scenario)
Arithmetic	SUM, SUBTOTAL, PRODUCT	range or numbers	=SUM(B2:B10) – total sales for the month
Statistical	AVERAGE, MEDIAN, MIN, MAX, COUNT	range	=AVERAGE(C2:C15) – average test score
Logical	IF, IFS, AND, OR, NOT	logical_test, value_if_true, value_if_false (or multiple tests)	=IF(E2>=75,"Distinction",IF(E2>=60,"Merit","Fail"))
Lookup & reference	VLOOKUP, HLOOKUP, INDEX, MATCH, XLOOKUP (if available)	lookup_value, table_array, col_index_num, [range_lookup]	=VLOOKUP("P123",Sheet2!A:D,4,FALSE) – price of product P123
Date & time	DATE, TIME, NOW, TODAY, DATEDIF, NETWORKDAYS	year, month, day … or range	=DATEDIF(A2,TODAY(),"D") – days since order date
Text	CONCAT, LEFT, RIGHT, MID, LEN, UPPER, LOWER, TRIM	text strings or cell references	=CONCAT(A2," – ",B2) – combine first & last name
Error handling	IFERROR, IFNA, ISERROR, ISNUMBER	value, value_if_error	=IFERROR(VLOOKUP(D2,Products!A:B,2,FALSE),"Not found")
Counting with criteria	COUNTIF, COUNTIFS, SUMIF, SUMIFS	range, criteria (or multiple pairs)	=COUNTIFS(Status!B2:B30,"Approved",Status!C2:C30,">=80")

5.1 SUM

SUM(number1, [number2, …])

Example: =SUM(A2:A10) returns the total of cells A2‑A10.

5.2 IF / IFS

IF(logical_test, value_if_true, value_if_false)

IFS(test1, result1, [test2, result2], …)

Nested IF example (grade classification):

=IF(B2>=75,"Distinction",IF(B2>=60,"Merit",IF(B2>=40,"Pass","Fail")))

Equivalent IFS formula:

=IFS(B2>=75,"Distinction",B2>=60,"Merit",B2>=40,"Pass",TRUE,"Fail")

5.3 VLOOKUP

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Exact match example (price list):

=VLOOKUP("E045",A2:C10,3,FALSE)

5.4 COUNTIF / COUNTIFS

COUNTIF(range, criteria)

COUNTIFS(range1, criteria1, [range2, criteria2], …)

Count sales greater than £500:

=COUNTIF(D2:D20,">500")

Count “Approved” records with a score ≥ 80:

=COUNTIFS(Status!B2:B30,"Approved",Status!C2:C30,">=80")

5.5 Additional useful functions

• SUMIF / SUMIFS – conditional totals.
• INDEX + MATCH – flexible look‑ups (horizontal or vertical).
• IFERROR – graceful handling of lookup failures.
• Dynamic‑array functions (if the exam board permits): FILTER, SORT, UNIQUE.

---

6. Sorting, Filtering & Extracting Data

• Sort: one‑level or multi‑level ascending/descending (Data → Sort).
• Auto‑filter: quick filter by column values; use Custom Filter for complex criteria.
• Advanced filter (or “Extract Unique Records”): create a separate list that meets multiple criteria or returns distinct values.

Example – Advanced filter for unique “North” region customers

1. Define criteria range: G1=Region, G2="North".
2. Select the data table.
3. Data → Advanced → “Copy to another location”, set Criteria range to G1:G2, Copy to J1, tick “Unique records only”.
4. Click OK; column J now contains a deduplicated list of North‑region customers.

---

7. Graphs & Charts

Charts help to communicate trends, comparisons and proportions. The Cambridge exam expects students to create, format and choose an appropriate chart type.

Common chart types and typical uses

Chart type	Best for
Column / Bar	Comparing quantities across categories (e.g., sales by region).
Line	Showing trends over time (e.g., monthly revenue).
Pie	Displaying parts of a whole (e.g., market‑share percentages).
Combination (column + line)	Two related data series with different units (e.g., sales vs profit).
Scatter	Examining correlation between two numeric variables.

Creating a chart (step‑by‑step)

1. Select the data range, including headings (e.g., A1:B13 for months and sales).
2. Insert → Charts → choose the required type (e.g., Line).
3. Use the Chart Design tab to add a chart title, axis titles, and a legend.
4. Format the axis (set minimum/maximum, major unit) and apply a style that matches the report.
5. For a combination chart, select the series you want as a line, right‑click → “Change Series Chart Type” → choose “Line”. Add a secondary axis if the units differ.

Practice Task – Sales trend with profit on a secondary axis

1. In columns A–C enter Month, Sales (£), Profit (£) for Jan‑Dec.
2. Select A1:C13, Insert → Combo Chart → “Clustered Column – Line on Secondary Axis”.
3. Give the chart a title “Monthly Sales & Profit”, label both axes, and apply a colour scheme.

---

8. Import & Export of Data

Examiners may require you to bring data into a workbook or to produce a file for external use.

Importing text‑based data (CSV / TXT)

1. Data → Get Data → From Text/CSV.
2. Browse to the file, click Import.
3. In the preview dialog, choose the correct delimiter (comma, tab, semicolon) and data type for each column.
4. Click Load to place the data on a new worksheet.

Exporting data

• CSV: File → Save As → choose “CSV (Comma delimited)”. Useful for transferring tables to other applications.
• PDF: File → Export → Create PDF/XPS Document – ensures layout and formatting are preserved for submission.
• Image: Select the chart or range, right‑click → Copy as Picture → paste into a Word document or slide.

---

9. Testing a Spreadsheet (AO3)

Testing confirms that the model works correctly with a range of inputs.

Test‑plan template

Test #	Input data	Expected result	Actual result	Pass/Fail
1	Typical numeric values (e.g., 10, 20)	Correct totals, look‑ups, charts update
2	Extreme values (0, 9999)	No overflow, formatting remains appropriate
3	Invalid data (text in numeric field)	#VALUE! caught by IFERROR; validation prevents entry
4	Empty cells where formulas expect data	Formulas return 0 or blank as intended

Record any discrepancies, adjust formulas, validation rules or formatting, and re‑run the relevant tests.

---

10. Summary Table of Core Functions

Function	Purpose	Key arguments	Example formula
SUM	Add numbers in a range	range or numbers	=SUM(A1:A10)
AVERAGE	Calculate mean value	range	=AVERAGE(B2:B12)
MIN / MAX	Find smallest / largest value	range	=MIN(C2:C20)
IF	Single conditional test	logical_test, value_if_true, value_if_false	=IF(D2>=50,"Pass","Fail")
IFS	Multiple conditional tests without nesting	test1, result1, [test2, result2]…	=IFS(E2>=75,"Distinction",E2>=60,"Merit",E2>=40,"Pass",TRUE,"Fail")
VLOOKUP	Vertical lookup (exact or approximate)	lookup_value, table_array, col_index_num, [range_lookup]	=VLOOKUP("P123",Products!A:D,4,FALSE)
INDEX + MATCH	Flexible lookup (horizontal or vertical)	INDEX(array, MATCH(lookup_value, lookup_array,0))	=INDEX(Prices!C:C, MATCH(G2, Prices!A:A,0))
COUNTIF / COUNTIFS	Count cells meeting one or more criteria	range, criteria (or multiple pairs)	=COUNTIFS(Status!B2:B30,"Approved",Status!C2:C30,">=80")
SUMIF / SUMIFS	Sum values that meet criteria	range, criteria, [sum_range]	=SUMIFS(Sales!D:D, Sales!B:B,"North", Sales!C:C,">=500")
DATE / TODAY / NOW	Create or return dates and times	year, month, day (for DATE)	=DATE(2025,12,31)
IFERROR	Replace an error with a custom value	value, value_if_error	=IFERROR(VLOOKUP(D2,Products!A:B,2,FALSE),"Not found")
CONCAT / TEXTJOIN	Combine text strings	text1, [text2, …] (separator optional for TEXTJOIN)	=CONCATENATE(A2," – ",B2)
FILTER (dynamic array)	Return a subset of a range that meets criteria	array, include, [if_empty]	=FILTER(A2:C100, B2:B100="North")

---

These notes cover every sub‑topic required by Cambridge 9626 Topic 8 and provide practical tasks, examples and testing guidance to help students achieve full marks in both written and practical examinations.