Be able to use functions including sum, average, maximum, minimum, integer, rounding, counting, LOOKUP, VLOOKUP, HLOOKUP, XLOOKUP, IF

Topic 20 – Spreadsheets: Functions, Data Manipulation & Presentation

Learning Objective

By the end of this lesson students will be able to:

• Build a simple data model – set up headings, define data types, apply appropriate cell formatting.
• Use the core spreadsheet functions confidently and correctly (SUM, AVERAGE, MAX, MIN, INT, ROUND, ROUNDUP, ROUNDDOWN, COUNT, COUNTA, LOOKUP, VLOOKUP, HLOOKUP, XLOOKUP, IF).
• Apply absolute, mixed and relative cell references and understand order of operations.
• Sort, filter and search data, and use conditional formatting to highlight key results.
• Present data effectively – adjust page‑layout, add headers/footers, create basic charts and export the worksheet.

1. Building a Data Model

Checklist for a well‑structured worksheet

1. Enter clear column headings (e.g., Student ID, Name, Mark, Attendance, Grade).
2. Choose the correct data type for each column:

   • Numbers – use Number format.
   • Percentages – format as Percentage (e.g., 0.85 → 85%).
   • Currency – format as Currency (£, $, etc.).
   • Text – leave as General or Text.

3. Apply consistent number formatting (decimal places, thousand separators) to improve readability.
4. Define any named ranges that will be reused (e.g., Marks_Q1 for B2:B21).
5. Lock headings with Freeze Panes so they stay visible when scrolling.

1.1 Formula Fundamentals

1.1.1 Order of Operations (PEMDAS)

• Parentheses – calculate inside brackets first.
• Exponents – =2^3 → 8.
• Multiplication / Division – left‑to‑right.
• Addition / Subtraction – left‑to‑right.

Example: =3+2*4 → 3 + (2 × 4) = 11 (not 20).

1.1.2 Cell‑reference Types

1.1.3 Named Ranges (optional but useful)

Define a range once (e.g., SalesQ1) and use the name in formulas: =SUM(SalesQ1). This improves readability and reduces errors.

2. Core Arithmetic Functions

3. Integer & Rounding Functions

• INT – Truncates toward zero (always rounds down).
  

  =INT(4.7) → 4

• ROUND – Rounds to a specified number of decimal places.
  

  =ROUND(3.14159, 2) → 3.14

• ROUNDUP – Rounds away from zero.
  

  =ROUNDUP(2.31, 1) → 2.4

• ROUNDDOWN – Rounds toward zero.
  

  =ROUNDDOWN(2.39, 1) → 2.3

4. Counting Functions

When to use which? Use COUNT for numeric tallies (e.g., marks). Use COUNTA when you need to count any entry (e.g., names, comments).

5. Lookup Functions

5.1 LOOKUP (approximate match)

• Syntax: =LOOKUP(lookupvalue, lookupvector, [result_vector])
• Example: =LOOKUP(85, A2:A10, B2:B10) – finds 85 in column A and returns the matching entry from column B.
• Note: The lookup vector must be sorted in ascending order.

5.2 VLOOKUP (vertical)

• Syntax: =VLOOKUP(lookupvalue, tablearray, colindexnum, [range_lookup])
• Exact match (recommended): =VLOOKUT("Smith", A2:D20, 3, FALSE) – returns column C where “Smith” appears in column A.
• range_lookup: FALSE = exact, TRUE = approximate (requires sorted data).

5.3 HLOOKUP (horizontal)

• Syntax: =HLOOKUP(lookupvalue, tablearray, rowindexnum, [range_lookup])
• Example: =HLOOKUP("Q2", A1:E5, 4, FALSE) – finds “Q2” in row 1 and returns the value from row 4 of that column.

5.4 XLOOKUP (flexible replacement)

• Syntax: =XLOOKUP(lookupvalue, lookuparray, returnarray, [ifnotfound], [matchmode], [search_mode])
• Example: =XLOOKUP(2023, A2:A10, B2:B10, "Not found")
• match_mode: 0 = exact (default), -1 = exact‑or‑next‑smaller, 1 = exact‑or‑next‑larger, 2 = wildcard.
• search_mode: 1 = first‑to‑last (default), -1 = last‑to‑first.

6. Logical Function – IF

• Syntax: =IF(logicaltest, valueiftrue, valueif_false)
• Simple example: =IF(D5>=50, "Pass", "Fail")

6.1 Nesting IFs (multiple criteria)

=IF(E2>=90, "A",
IF(E2>=80, "B",
IF(E2>=70, "C",
IF(E2>=60, "D", "F"))))

6.2 Combining IF with AND / OR

• Pass if ≥ 50 and attendance ≥ 75 %:
  

  =IF(AND(D5>=50, E5>=75%), "Pass", "Fail")

• Pass if ≥ 50 or extra credit ≥ 10:
  

  =IF(OR(D5>=50, F5>=10), "Pass", "Fail")

7. Data Manipulation – Sorting, Filtering & Conditional Formatting

7.1 Sorting

1. Select the range (or click any cell in a table).
2. Data ► Sort A‑Z / Sort Z‑A for a single column.
3. For multi‑level sort, choose Custom Sort, add levels, and specify column, order, and whether to sort left‑to‑right.

7.2 Filtering

1. Select the header row and click Filter (Data ► Filter).
2. Use the drop‑down arrows to:

   • Show only rows that meet a condition (e.g., marks ≥ 70).
   • Search for specific text.
   • Apply number filters such as “Top 10%”.

7.3 Conditional Formatting (visual data manipulation)

• Home ► Conditional Formatting ► Highlight Cells Rules or New Rule.
• Common examples for IGCSE:

  • Highlight marks ≥ 90 (green).
  • Show “Fail” in red if =IF(D5<50,TRUE,FALSE).
  • Colour‑scale to visualise the distribution of scores.

8. Presenting Data – Layout, Printing & Charts

8.1 Page‑Setup & Printing

• Page Layout ► Orientation – Portrait or Landscape.
• Margins – Normal, Wide, Narrow, or custom values.
• Scaling – Fit Sheet on One Page or set a specific %.
• Headers/Footers – Insert file name, sheet name, page number, or date (Insert ► Header & Footer).
• Print Preview – check that headings repeat on each printed page (Page Layout ► Print Titles → “Rows to repeat”).

8.2 Basic Chart Creation

1. Select the data range (including headings).
2. Insert ► Chart ► choose a Column, Bar, Line or Pie chart.
3. Use the Chart Design tab to switch rows/columns, add a chart title, and label axes.
4. Resize and move the chart to a convenient location on the sheet.

8.3 Exporting

• Save as .xlsx for full functionality.
• Export to PDF (File ► Export ► Create PDF/XPS Document) for easy sharing.
• Export to CSV (File ► Save As ► CSV) when only raw data is required.

9. Combining Functions – Example Scenarios

• Average of the three highest scores:
  

  =AVERAGE(LARGE(B2:B10,{1,2,3}))

• Total sales after rounding each month up to the nearest whole pound:
  

  =SUM(ROUNDUP(C2:C13,0))

• Count of students who passed (≥ 50) and have attendance ≥ 75 %:
  

  =COUNTIFS(D2:D31,">=50",E2:E31,">=75%") (optional advanced function).

10. Common Errors & Quick Fixes

11. Quick‑Reference Table (Cambridge IGCSE 0417 Syllabus Alignment)

12. Suggested Practice Activities

• Create a sales worksheet; use SUM for monthly totals and ROUNDUP to round each month’s sales up to the nearest £.
• Enter a class‑mark table; calculate AVERAGE, MAX, MIN, then assign grades with a nested IF formula.
• Build a product‑price list; retrieve prices using VLOOKUP (exact match) and repeat with XLOOKUP including a custom “Not found” message.
• Count the number of pupils who achieved a numeric score using COUNT and the total entries (including comments) with COUNTA.
• Sort the marks column descending, then apply a filter to show only scores ≥ 80.
• Apply conditional formatting to highlight:

  • Marks ≥ 90 (green fill).
  • Failing marks (<50) in red.

• Insert a column chart of monthly sales; set the page orientation to Landscape, add a header “Sales Report 2023”, and export the sheet as a PDF.
• Copy a formula across a table and observe how relative, absolute and mixed references behave; adjust with $ as required.