Know and understand the difference between a formula and a function

20 Spreadsheets – Formula vs Function (Cambridge IGCSE ICT 0417)

0. Syllabus Topic‑Map (Section 20 & Related Sections)

1. What Is a Formula?

• A formula is any expression that the spreadsheet evaluates to produce a result.
• All formulas start with an equals sign =.
• Typical components that may appear in a formula:
  • Cell references – relative (A1), absolute ($A$1), or mixed (A$1, $A1).
  • Constants – numbers (e.g., 100) or text strings (e.g., "Pass").
  • Arithmetic operators – +, -, *, /, ^ (exponent).
  • Parentheses – control the order of evaluation.
• Order of operations (PEMDAS/BODMAS) used by the spreadsheet:
  1. Parentheses
  2. Exponents
  3. Multiplication and division – evaluated left‑to‑right
  4. Addition and subtraction – evaluated left‑to‑right

  Example: =A1 + B1 * 2 is interpreted as A1 + (B1 × 2).

• Common pitfalls to watch for:
  • Implicit conversion – adding a number to a text string returns #VALUE! unless the text can be coerced.
  • Division by zero produces #DIV/0!.
  • Circular references cause #REF! or a warning dialog.
  • Forgotten absolute references when copying formulas can lead to incorrect results.

2. What Is a Function?

A function is a predefined calculation supplied by the spreadsheet program. Its syntax is:

=FUNCTION_NAME(argument1, argument2, …)

Functions save time, reduce errors and often include built‑in error handling.

Analysis / Evaluation tip (AO3): When choosing a lookup function, consider accuracy (exact vs approximate match), maintainability (XLOOKUP can replace both VLOOKUP and HLOOKUP), and performance (XLOOKUP is generally faster on large tables).

3. Using Functions Inside Formulas (Nested Functions)

Functions can be combined, or placed inside a larger arithmetic expression, to create powerful calculations.

=IFERROR(VLOOKUP(A2,ProductTable,3,FALSE),0)

• VLOOKUP searches for the product code in ProductTable and returns the sales figure from column 3.
• IFERROR replaces the #N/A error (product not found) with 0.

Another example – calculate the average of only positive numbers in a range:

=AVERAGEIF(B1:B20,">0")

4. Named Cells / Named Ranges

• Definition: Assign a meaningful name to a single cell or a block of cells (e.g., TaxRate for $B$1).
• How to create (typical steps):
  1. Select the cell or range.
  2. Click in the Name Box (left of the formula bar), type the desired name (no spaces, start with a letter), and press Enter.
• Benefits:
  • Formulas become easier to read: =Price * TaxRate vs =C2*$B$1.
  • When the referenced cell moves, the name automatically follows.
  • Names can be used across worksheets in the same workbook.

5. Key Differences Between Formulas and Functions

6. Practical Spreadsheet Skills Checklist (Paper 2 & 3)

• Enter data accurately, using appropriate data types (numbers, dates, text).
• Apply cell formatting (number formats, alignment, borders) to improve readability.
• Create and use named ranges for key constants.
• Write at least three different formulas that combine arithmetic operators and cell references.
• Use a minimum of five different functions, including at least one lookup, one conditional, and one aggregation function.
• Demonstrate nesting by embedding one function inside another (e.g., IFERROR(VLOOKUP(...),0)).
• Apply absolute, relative, and mixed references correctly when copying formulas.
• Sort data and apply a filter to display a subset of rows.
• Insert a chart that automatically updates when the source data changes.
• Protect a worksheet or specific cells to prevent accidental editing (optional – AO2).

7. Decision Flow – When to Use a Formula or a Function

8. Quick Reference Cheat‑Sheet