Be able to replicate formulae using absolute and relative cell references where appropriate

Topic 20 – Spreadsheets (IGCSE 0417)

Learning objectives

• Identify and use relative, absolute and mixed cell references.
• Apply the order of operations (PEMDAS/BODMAS) correctly in formulas.
• Use the core spreadsheet functions required by the syllabus: SUM, AVERAGE, MIN, MAX, COUNT, IF, LOOKUP, VLOOKUP, HLOOKUP.
• Create and employ named ranges / named cells to make formulas easier to read.
• Copy (replicate) formulas accurately across rows, columns and rectangular blocks, preserving the required references.
• Recognise common pitfalls and avoid them.

1. Cell‑reference types

When to use each type

• Relative – data that must change with each copy (quantities, prices, lookup values).
• Absolute – constants such as tax rates, exchange rates, or named cells.
• Mixed – needed when copying only vertically or only horizontally:
  • $A1 – copy down rows, keep the same column (e.g., a column of discount percentages stored vertically).
  • A$1 – copy across columns, keep the same row (e.g., a single‑row markup percentage).

Copying a rectangular block

When a range of cells (e.g., a 2 × 3 table) is copied, each formula inside the block adjusts its references in both dimensions. Use mixed references if a particular row or column must stay constant while the other dimension changes.

2. Order of operations (PEMDAS / BODMAS)

Spreadsheets evaluate a formula in the following sequence:

1. Parentheses / brackets – calculations inside ( ) first.
2. Exponents – ^ operator.
3. Multiplication and division – left‑to‑right.
4. Addition and subtraction – left‑to‑right.

Example with nested parentheses

=IF((B2*C2)>100, (B2*C2)*0.9, (B2*C2)*1.05)

Explanation:

• Inner parentheses calculate B2*C2 first.
• The result is compared with 100.
• Depending on the test, a second pair of parentheses applies the appropriate multiplier.

3. Core functions (required by the syllabus)

4. Named ranges & named cells

Named items behave like absolute references and make formulas easier to read.

How to create a name (Excel)

1. Select the cell or range you want to name.
2. Go to the Formulas tab → click Define Name.
3. In the New Name dialog, type a meaningful name (e.g. VAT_RATE).
4. Check that the correct range appears in the Refers to box and click OK.

How to create a name (Google Sheets)

1. Select the cell or range.
2. Menu: Data ▶ Named ranges.
3. Enter the name (e.g. VAT_RATE) and click Done.

Once defined, you can use the name directly in any formula, e.g. =D2*VAT_RATE. The name is automatically treated as an absolute reference.

5. Replicating (copying) formulas

1. Select the cell that contains the original formula.
2. Hover over the fill handle (the small square at the bottom‑right corner).
3. Drag:
   • Downwards – copies the formula to lower rows (relative rows change, absolute rows stay).
   • Sideways – copies the formula to the right (relative columns change, absolute columns stay).
   • Both directions – select a block and drag the fill handle to fill a rectangle; each cell’s references shift according to its position.
4. Release the mouse – Excel/Sheets automatically adjusts relative references and leaves absolute/mixed references unchanged.

Horizontal‑copy illustration

Original formula in C2: =A2*$B$1

• Copy to D2 → becomes =B2*$B$1 (column changes, row stays, $B$1 stays fixed).
• Copy to E2 → becomes =C2*$B$1.

6. Practical example – Shop sales (uses all concepts)

Steps:

1. Enter the three formulas in D2, F2 and G2.
2. Drag the fill handle down to row 4 – the references adjust as described.
3. Define a named range VAT_RATE for cell E1 and replace $E$1 with VAT_RATE in F2 (and copy down).
4. Enter the COUNT formula in H2 to show how many numeric entries exist in the Quantity column.

7. Mixed‑reference example – Fixed discount across columns

Goal: Apply a 10 % discount (stored in $B$1) to monthly prices in C2:E2 (Jan‑Mar) and obtain discounted prices in C3:E3.

1. Enter 0.10 in B1 and (optionally) name it DISCOUNT.
2. In C3 type =C2*(1-$B$1)  or  =C2*(1‑DISCOUNT).
3. Copy the formula across to D3 and E3. Because the column part of C2 is relative, it becomes D2 and E2. The row‑fixed part $B$1 (or the name DISCOUNT) stays constant.

8. Common pitfalls & how to avoid them

• Missing “$” signs – the reference changes when copied; always double‑check the formula bar.
• Using absolute references for data cells (e.g., $B$2) prevents the formula from adapting to each row/column.
• Choosing the wrong mixed reference – decide whether the column or the row must stay fixed before copying.
• Named‑range errors – a name that has not been defined yields #NAME?. Verify the name in the Name Manager (Excel) or Named ranges pane (Sheets).
• Copying a rectangular block without considering both dimensions – mixed references are often needed when a formula must keep a particular row or column constant while the other dimension varies.

9. Quick reference cheat‑sheet

10. Practice exercise

Complete the tasks in a new spreadsheet. Use absolute or mixed references where required, and name the VAT‑rate cell VAT_RATE for extra credit.

1. Re‑create the table from Section 6 (products, quantity, unit price). Put the VAT rate 0.20 in cell E1.
2. In D2 enter =B2*C2 and copy down to the last product row.
3. Define a named range VAT_RATE that refers to E1.
4. In F2 enter =D2*VAT_RATE (or =D2*$E$1) and copy down.
5. In G2 enter =D2+F2 and copy down.
6. Add a discount of 15 % in cell B1. In H2 calculate the discounted total price using a mixed reference so the discount stays fixed when copying across columns:

   =D2*(1-$B$1)

   Copy the formula across to I2 and J2 (simulate other months).
7. Enter =COUNT(B2:B10) in a convenient cell to count how many numeric quantities are entered.
8. Check that:
   • All VAT calculations use the same rate (named range or absolute reference).
   • The discount column always refers to B1 even after copying.

11. Extension tasks (AO2 – AO3)

• Data validation: Restrict the VAT‑rate cell (E1) to a value between 0 and 0.25. (Data ▶ Data validation ▶ Criteria = Decimal between 0 and 0.25.)
• Conditional formatting: Highlight any product where the grand total (column G) exceeds £100. (Home ▶ Conditional Formatting ▶ New Rule ▶ Format only cells that are greater than 100.)
• Charting: Create a clustered column chart showing Total Price, VAT Amount and Grand Total for each product.
• Evaluation (≈80 words): Write a short paragraph comparing the advantages of using named ranges versus absolute references in large worksheets (e.g., readability, ease of updating, reduced error risk).

12. Suggested diagram