Be able to create formulae using cell references

Spreadsheets – Creating Formulae Using Cell References

Learning Objective

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

• Create accurate formulae using cell references.
• Distinguish between relative, absolute and mixed references.
• Define, name and manage cells or ranges.
• Apply arithmetic operators and a wide range of built‑in functions (including nested functions).
• Copy, fill and lock formulae correctly.
• Use essential spreadsheet tools such as merging cells, conditional formatting, data validation, importing data, printing set‑up and sheet protection.

Key Concepts

• Cell‑reference notation (A1, $A$1, $A1, A$1)
• Relative, absolute and mixed references
• Named cells and named ranges (scope, Name Manager)
• Arithmetic operators (+, –, *, /, ^)
• Core functions (SUM, AVERAGE, MIN, MAX, IF, VLOOKUP / XLOOKUP, ROUND)
• Additional useful functions (COUNT, COUNTA, COUNTIF, SUMIF, AVERAGEIF, AND, OR)
• Nested functions
• Copying, filling and the fill handle
• Order of operations (PEMDAS)
• Conditional formatting and data validation
• Importing / linking external data
• Printing set‑up and worksheet security

1. Cell Reference Basics

A cell reference tells the spreadsheet where a value is stored. The column is identified by a letter and the row by a number, e.g. A1. When a formula is copied, the reference can change (relative) or stay fixed (absolute).

2. Relative References

Relative references adjust automatically when a formula is moved or copied.

1. Enter =B2+C2 in cell D2.
2. Drag the fill handle down to D3. The formula becomes =B3+C3.

3. Absolute and Mixed References

Reference type	Notation	Behaviour when copied
Absolute column & row	$A$1	Never changes
Absolute column only	$A1	Column stays, row changes
Absolute row only	A$1	Row stays, column changes

Example – tax calculation:

1. Enter the tax rate (e.g. 0.15) in F1.
2. In E2 type =B2*$F$1.
3. Copy the formula down. B2 becomes B3, but $F$1 remains fixed.

4. Named Cells and Named Ranges

• Why use names? They make formulas easier to read and prevent errors caused by moving the referenced cell.
• Scope – A name is workbook‑wide by default; it can be used on any sheet unless you create a sheet‑specific name.
• Creating a name:
  1. Select the cell or range.
  2. Go to Formulas ▶ Define Name (or use the Name Box).
  3. Enter a meaningful name (e.g. TaxRate or SalesQ1).
• Managing names – Open Formulas ▶ Name Manager to edit, delete or change the scope of existing names.
• Using a name – Replace a cell reference with the name, e.g. =B2*TaxRate.

5. Arithmetic Operators

• Addition: +
• Subtraction: -
• Multiplication: *
• Division: /
• Exponentiation: ^

Compound‑interest example

Mathematical form: \(A = P(1+r)^n\)

Spreadsheet formula: =B2*(1+B3)^B4 (where B2 = principal, B3 = rate, B4 = periods).

6. Core Functions

Function	Syntax	Purpose
SUM	=SUM(A1:A10)	Adds all numbers in the range.
AVERAGE	=AVERAGE(B1:B5)	Calculates the mean.
MIN	=MIN(C1:C8)	Finds the smallest value.
MAX	=MAX(C1:C8)	Finds the largest value.
IF	=IF(logical_test, value_if_true, value_if_false)	Returns one value when a condition is true, another when false.
VLOOKUP	=VLOOKUP(lookup_value, table_array, col_index, [range_lookup])	Searches a table vertically.
XLOOKUP	=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])	Modern, flexible lookup (vertical or horizontal).
ROUND	=ROUND(number, num_digits)	Rounds a number to a specified number of decimal places.

7. Additional Useful Functions (Syllabus Requirement)

Function	Syntax	Typical Use
COUNT	=COUNT(A1:A10)	Counts numeric entries.
COUNTA	=COUNTA(A1:A10)	Counts all non‑blank entries (numbers, text, dates).
COUNTIF	=COUNTIF(range, criteria)	Counts cells that meet a single condition (e.g. =COUNTIF(B2:B10,">100")).
SUMIF	=SUMIF(range, criteria, [sum_range])	Sums values that meet a condition (e.g. =SUMIF(A2:A10,"<50",B2:B10)).
AVERAGEIF	=AVERAGEIF(range, criteria, [average_range])	Average of cells that meet a condition.
AND / OR	=AND(condition1, condition2, …) =OR(condition1, condition2, …)	Combine multiple logical tests; often used inside IF (e.g. =IF(AND(A2>0,B2>0), "OK", "Check")).

8. Nested Functions

Functions can be placed inside other functions to perform several operations in one formula.

Example – round the average of a range to two decimals

=ROUND(AVERAGE(A1:A10),2)

Example – apply a 15 % tax only if the amount exceeds 100, otherwise show 0

=IF(A2>100, A2*0.15, 0)

Example – give a discount of 10 % when both quantity > 20 and price > £5

=IF(AND(B2>20, C2>5), C2*0.9, C2)

9. Copying, Filling and the Fill Handle

1. Select the cell containing the formula.
2. Hover over the small square (fill handle) at the bottom‑right corner.
3. Drag down, across, or double‑click to fill the required range.

• Relative references adjust automatically.
• Absolute or named references stay fixed.
• Hold Ctrl while dragging to copy without adjusting references (useful for absolute‑only formulas).

10. Order of Operations (PEMDAS)

Spreadsheets evaluate expressions in this order:

1. Parentheses ( )
2. Exponents ^
3. Multiplication * and Division / (left‑to‑right)
4. Addition + and Subtraction – (left‑to‑right)

Example

=(A1+B1)*C1-D1/E1

11. Conditional Formatting

Changes a cell’s appearance based on its value.

1. Select the range.
2. Home ▶ Conditional Formatting → choose a rule (e.g., “Greater Than”, “Data Bars”, “Colour Scales”).
3. Set the formatting options (colour, font style, etc.) and click OK.

12. Data Validation

Restricts the type of data that can be entered.

1. Select the target cells.
2. Data ▶ Data Validation.
3. Choose a validation type (Whole number, List, Date, etc.) and define the criteria.
4. Optionally add an input message or error alert.

13. Importing & Linking External Data

• Import CSV/TXT: Data ▶ Get & Transform ▶ From Text/CSV. Follow the wizard to set delimiters and data types.
• Link to another workbook: Use a formula such as =‘[Budget.xlsx]Sheet1’!$B$2. The same relative/absolute rules apply to the external reference.

14. Printing & Page Layout

Prepare the sheet so it meets exam‑style requirements.

1. Page Layout ▶ Print Area → Set Print Area (e.g., A1:D20).
2. Choose orientation (Portrait/Landscape) and scaling (e.g., Fit Sheet on One Page).
3. Add headers/footers via Insert ▶ Header & Footer.
4. Check the layout with File ▶ Print Preview before printing.

15. Spreadsheet Security

• Protect a worksheet: Review ▶ Protect Sheet → set a password and tick the actions you want to allow (e.g., “Select unlocked cells”).
• Protect the whole workbook: Review ▶ Protect Workbook.
• Remember: passwords must be stored securely; forgetting them can lock you out of the file.

16. Common Mistakes to Avoid

• Forgetting the leading = – the cell will display text, not calculate.
• Using absolute references where a relative reference is needed – this can cause the same constant to be used everywhere.
• Referencing empty cells – they return 0 in most arithmetic operations, which may be unintended.
• Incorrect range notation (e.g., A1-A5 instead of A1:A5).
• Misspelling function names (e.g., “A · ERAGE” → “AVERAGE”).
• Leaving the fill handle on when you only wanted a single copy – use Ctrl + Enter to keep the original reference.
• Creating a name that conflicts with a cell address (e.g., naming a range A1) – avoid this to prevent formula errors.

17. Practice Exercise (Exam‑style)

Complete the steps in a new spreadsheet.

1. Enter the numbers 10, 20, 30, 40, 50 in cells A2:A6.
2. In C1 type the tax rate 0.15 and name the cell TaxRate (Formulas ▶ Define Name).
3. In B2 calculate 15 % tax on A2 using the named reference: =A2*TaxRate. Copy down to B6.
4. In D2 compute the total (value + tax) with a relative reference: =A2+B2. Copy down to D6.
5. In D7 find the grand total using =SUM(D2:D6).
6. Apply conditional formatting to D2:D6 to highlight totals greater than 40 (e.g., light‑green fill).
7. Set data validation on column A to allow only whole numbers between 0 and 100.
8. Print preview the sheet, set the print area to A1:D7, and choose “Fit Sheet on One Page”.

18. Summary

Mastering cell references—relative, absolute, mixed and named—is the foundation of reliable spreadsheet modelling. Combined with the correct use of arithmetic operators, a broad suite of functions (including logical, counting and conditional functions), and spreadsheet tools such as conditional formatting, data validation and protection, you can build efficient, accurate and exam‑ready spreadsheets for the Cambridge IGCSE ICT 0417 assessment.