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 in a spreadsheet by using cell references, understand the difference between relative and absolute references, and apply these concepts to common calculations.

Key Concepts

• Cell reference notation (A1, $A$1, A$1, $A1)
• Relative vs absolute references
• Basic arithmetic operators (+, –, *, /, ^)
• Using functions with cell ranges (SUM, A \cdot ERAGE, MIN, MAX)
• Copying and filling formulae
• Order of operations (PEMDAS)

1. Cell Reference Basics

A cell reference identifies the location of a cell in a worksheet. The column is denoted by a letter and the row by a number, e.g., A1.

When a formula is copied to another cell, the references can change automatically (relative) or stay fixed (absolute).

2. Relative References

Default references are relative. They adjust when the formula is moved or copied.

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

3. Absolute References

Use the dollar sign ($) to lock a column, a row, or both.

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

Example:

1. In E2 type =B2*$F$1 where F1 holds a tax rate.
2. Copy the formula down to E3. The reference to F$1 remains fixed, while B2 becomes B3.

4. Basic Arithmetic Operators

Spreadsheets support the following operators:

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

Example calculation for compound interest:

$$A = P(1 + r)^n$$

In a spreadsheet: =B2*(1+B3)^B4 where B2 = principal, B3 = interest rate, B4 = number of periods.

5. Using Functions with Ranges

Functions often require a range of cells, written as StartCell:EndCell.

Function	Syntax	Purpose
SUM	=SUM(A1:A10)	Adds all numbers in the range
A \cdot ERAGE	=A \cdot ERAGE(B1:B5)	Calculates the mean
MIN	=MIN(C1:C8)	Finds the smallest value
MAX	=MAX(C1:C8)	Finds the largest value

Combining functions with absolute references:

1. Place the tax rate in $F$1.
2. Calculate total sales in G2 with =SUM(B2:E2).
3. Calculate tax amount in H2 with =G2*$F$1.

6. Copying and Filling Formulae

When you drag the fill handle, the spreadsheet automatically adjusts relative references. Use absolute references to keep constants fixed.

Steps:

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

7. Order of Operations (PEMDAS)

Spreadsheets evaluate expressions using the standard order:

1. Parentheses
2. Exponents
3. Multiplication and Division (left‑to‑right)
4. Addition and Subtraction (left‑to‑right)

Example:

$$\text{Result}= (A1 + B1) * C1 - D1 / E1$$

In a cell: =(A1+B1)*C1-D1/E1

8. Common Mistakes to Avoid

• Forgetting the = at the start of a formula.
• Using absolute references when a relative reference is needed, causing the same value to be used everywhere.
• Referencing empty cells, which may return 0 or an error.
• Incorrect range notation (e.g., A1-A5 instead of A1:A5).

9. Practice Exercise

Complete the following worksheet (use a spreadsheet application of your choice):

1. Enter the numbers 10, 20, 30, 40, 50 in cells A2:A6.
2. In B2 calculate 15 % tax on the value in A2 using an absolute reference to the tax rate in $C$1 (enter 0.15 in C1).
3. Copy the formula in B2 down to B6.
4. In D2 compute the total (value + tax) using a relative reference to the tax amount.
5. In D7 use =SUM(D2:D6) to find the grand total.

Summary

Understanding how to use cell references—both relative and absolute—is essential for building reliable spreadsheet models. Mastery of these concepts enables efficient calculations, accurate copying of formulas, and effective use of built‑in functions.