20 Spreads Sheets - Formulae with Cell References

This section focuses on creating formulae in spreadsheets using cell references. Understanding cell references is fundamental to performing calculations and data analysis effectively.

Understanding Cell References

A cell reference identifies a specific cell in a spreadsheet. Cell references are used in formulae to tell the spreadsheet which cells contain the values to be used in the calculation.

There are two main types of cell references:

• Relative Cell References: These references change when the formula is copied to another cell. For example, if a cell contains the formula `=A1+B1` and it's copied to the cell below, the reference will automatically change to `=A2+B2`.
• Absolute Cell References: These references do not change when the formula is copied. They are denoted by a dollar sign ($) before the column letter and/or row number. For example, `$A$1` will always refer to cell A1, even if the formula is copied. `$A1` fixes the column, but allows the row to change. `A$1` fixes the row, but allows the column to change.

Basic Formulae with Cell References

Let's look at some examples of creating formulae using cell references:

Addition

To add the values in cells A1 and B1, you would use the following formula:

=A1+B1

If you copy this formula to cell A2, it will automatically change to `=A2+B2`.

Subtraction

To subtract the value in cell B1 from the value in cell A1, you would use the following formula:

=A1-B1

Multiplication

To multiply the values in cells A1 and B1, you would use the following formula:

=A1*B1

Division

To divide the value in cell A1 by the value in cell B1, you would use the following formula:

=A1/B1

Using Absolute References

Sometimes you need to refer to a specific cell, regardless of where the formula is copied. This is where absolute references come in handy.

Consider a scenario where you want to calculate the total price of items, and the price of each item is stored in a column (e.g., column B). You want to sum all the prices in column B, regardless of which row the total price is in.

You can use an absolute reference to achieve this. Let's say the prices are in column B, starting from row 2. The formula to sum all the prices would be:

=SUM($B$2:$B$10)

This formula will always refer to the range B2 to B10, even if the formula is copied to other rows.

Examples and Exercises

Example 1: You have the sales figures for two products in cells C1 and C2. Create a formula in cell C3 that calculates the total sales.

=C1+C2

Example 2: You want to calculate the discount amount on an item. The original price is in cell D1 and the discount percentage is in cell D2 (e.g., 10%). Create a formula in cell D3 that calculates the discount amount.

=(D1*D2)/100

Exercise 1: Create a spreadsheet with the following data:

Item	Price	Quantity	Total
Apple	1.00	5
Banana	0.50	10
Orange	0.75	8

Create a formula in cell D3 that calculates the total price for the first item (Apple). Then, create a formula in cell D4 that calculates the total price for the second item (Banana). Finally, create a formula in cell D5 that calculates the total price for the third item (Orange). Use absolute references for the price column.

Exercise 2: You have a list of student scores in cells E1 to E5. Create a formula in cell F1 that calculates the average score of the students. Use absolute references to refer to the range of student scores.

=AVERAGE($E$1:$E$5)

Key Takeaways

Mastering cell references is crucial for effective spreadsheet use. Understanding the difference between relative and absolute references allows you to create dynamic and reliable formulae. Practice using different types of references to become proficient in spreadsheet calculations.