ICT 0417 - Spreadsheets

20. Arithmetic Operators in Formulae

This section covers the use of arithmetic operators in spreadsheet formulae. Understanding these operators is fundamental to performing calculations and analyzing data effectively.

Arithmetic Operators

Spreadsheet software provides four basic arithmetic operators:

• Addition: $+$ (Adds two or more numbers together)
• Subtraction: $- $ (Subtracts one number from another)
• Multiplication: $\times$ (Multiplies two or more numbers)
• Division: $\div$ (Divides one number by another)
• Exponentiation: $\wedge$ (Raises a number to a power)

Operator Precedence

Like in mathematics, arithmetic operators have a specific order of precedence. This determines the sequence in which calculations are performed. The order of precedence is generally as follows (from highest to lowest):

1. Parentheses ( ) - Calculations within parentheses are always performed first.
2. Exponentiation ($^\wedge$) - Calculations involving exponents are performed next.
3. Multiplication ($ \times $ ) and Division ($ \div $ ) - These are performed from left to right.
4. Addition ($ + $) and Subtraction ($ - $) - These are performed from left to right.

Examples of Formulae

Here are some examples demonstrating the use of arithmetic operators in spreadsheet formulae:

Addition

Example: If cell A1 contains 10 and cell B1 contains 5, the formula `=A1+B1` will return 15.

Formula: =A1+B1

Subtraction

Example: If cell A2 contains 20 and cell B2 contains 8, the formula `=A2-B2` will return 12.

Formula: =A2-B2

Multiplication

Example: If cell A3 contains 4 and cell B3 contains 6, the formula `=A3$\times$B3` will return 24.

Formula: =A3$\times$B3

Division

Example: If cell A4 contains 30 and cell B4 contains 5, the formula `=A4$\div$B4` will return 6.

Formula: =A4$\div$B4

Exponentiation

Example: If cell A5 contains 2 and cell B5 contains 3, the formula `=A5$\wedge$B5` will return 8 (2 to the power of 3).

Formula: =A5$\wedge$B5

Combining Operators

Example: If cell A6 contains 10, cell B6 contains 2, and cell C6 contains 3, the formula `=(A6+B6)$\times$C6` will return 36. This demonstrates the order of operations: addition is performed before multiplication.

Formula: =(A6+B6)$\times$C6

Using Relative and Absolute References

When using arithmetic operators in formulae, it's important to understand the difference between relative and absolute cell references. This affects how the formula changes when it's copied to other cells.

Reference Type	Example	Explanation
Relative	=A1+B1	When copied, the cell references change relative to the new location.
Absolute	=$A$1+$B$1	The cell references remain fixed, even when the formula is copied.

Using absolute references (e.g., `$A$1`) is useful when you want to refer to the same cell in multiple calculations.

Practice

To solidify your understanding, try the following practice problems:

1. Calculate the total cost of items in columns A and B, storing the result in cell C1.
2. Calculate the average of numbers in cells D1 to D5, storing the result in cell E1.
3. Calculate the area of a rectangle where length is in cell F1 and width is in cell G1, storing the result in cell H1.
4. Calculate the percentage increase from cell I1 to cell J1, storing the result in cell K1.