Mathematical Operators in a Spreadsheet
Introduction
Mathematical operators are symbols used in spreadsheet formulas to perform calculations. They allow users to calculate totals, differences, products, quotients and powers using numerical values or cell references.
When a formula contains more than one mathematical operator, the spreadsheet follows a specific order of operations to calculate the correct result. This is known as BIDMAS.
In this lesson, we will learn the five main mathematical operators, how they are used in spreadsheet formulas, and how the order of operations affects calculation results.
1. Mathematical Operators in Spreadsheets
A mathematical operator is a symbol that tells spreadsheet software which mathematical operation to perform.
Operators can be used with: - Direct numerical values. - Cell references. - A combination of numbers and cell references.
The five mathematical operators shown in the note are:
Operator Name Description Example Result
+ Addition Adds two or more values. =5+3 8
- Subtraction Subtracts one value from another. =10-4 6
* Multiplication Multiplies two or more values. =6*4 24
/ Division Divides one value by another. =20/5 4
^ Exponentiation Raises a number to a power. =2^3 8
Understanding Operators with Cell References
Instead of entering numbers directly into a formula, we can use cell references. A cell reference identifies the location of a value in a worksheet.
For example, consider the following worksheet:
Cell Value
A2 5 B2 3
The formula =A2+B2 adds the values stored in cells A2 and B2. The
result is 8.
Other examples include:
=A2-B2-- Subtracts B2 from A2.=A2*B2-- Multiplies A2 by B2.=A2/B2-- Divides A2 by B2.=A2^B2-- Raises the value in A2 to the power of B2.
<ExampleLabel>{=html}Example: Calculating Student
Marks</ExampleLabel>{=html}
Suppose a student receives 75 marks for Paper 1 and 85 marks for Paper
2. If the marks are entered into cells A2 and B2, the formula =A2+B2
calculates the total as 160.
2. Order of Operations (BIDMAS)
When a spreadsheet formula contains more than one mathematical operator, the operations are not necessarily performed from left to right. The spreadsheet follows a specific order called BIDMAS.
BIDMAS represents:
Letter Meaning Operator
B Brackets ( )
I Indices (Powers) ^
D Division /
M Multiplication *
A Addition +
S Subtraction -
Steps in the Order of Operations
1. Brackets ( )
Calculations inside brackets are performed first.
Formula: =(2+3)*4
- First: 2 + 3 = 5.
- Next: 5 × 4 = 20.
Result: 20
2. Indices (Powers) ^
Exponents or powers are calculated after brackets.
Formula: =2^3+4
- First: 2³ = 8.
- Next: 8 + 4 = 12.
Result: 12
3. Division and Multiplication / and *
Division and multiplication have the same priority. They are performed from left to right.
Formula: =20/5*2
- First: 20 ÷ 5 = 4.
- Next: 4 × 2 = 8.
Result: 8
4. Addition and Subtraction + and -
Addition and subtraction also have the same priority. They are performed from left to right.
Formula: =10-6+2
- First: 10 - 6 = 4.
- Next: 4 + 2 = 6.
Result: 6
Understanding BIDMAS with Examples
Formula Calculation Result
=2+3*4 2 + 12 14
=(2+3)*4 5 × 4 20
=10-6/2 10 - 3 7
=(10-6)/2 4 ÷ 2 2
=2^3+4 8 + 4 12
=(1+2)*3-4 3 × 3 - 4 5
=20/5*2 4 × 2 8
Important: Left-to-Right Rule
Division and multiplication have equal priority. Similarly, addition and subtraction have equal priority.
When operators have the same priority, calculations are performed from left to right.
For example, =20/5*2 is calculated as:
- 20 ÷ 5 = 4.
- 4 × 2 = 8.
It is not calculated as 20 ÷ (5 × 2).
Key Idea: BIDMAS determines the order in which mathematical operations are performed in a spreadsheet formula. Brackets are calculated first, followed by indices, division and multiplication, and finally addition and subtraction.
3. Using Mathematical Operators in a Worksheet
A worksheet can contain values, formulas and calculated results.
Consider the following example:
Value 1 Value 2 Formula Result 5 3 `=A2+B2` 8
10 4 `=A3-B3` 6
6 4 `=A4*B4` 24
20 5 `=A5/B5` 4
2 3 `=A6^B6` 8When a formula is entered and confirmed, the spreadsheet performs the calculation and displays the result in the cell.
The formula remains stored in the cell, even though the calculated result is displayed. If the values used in the formula change, the result is recalculated automatically.
4. Important Points to Remember
- Mathematical operators are used in spreadsheet formulas to perform calculations.
- Numbers, cell references or both can be used with mathematical operators.
- A spreadsheet formula must begin with an equal sign (
=). - The five operators covered are addition, subtraction, multiplication, division and exponentiation.
- BIDMAS determines the order of mathematical operations.
- Operators with the same priority are evaluated from left to right.
- The calculated result is displayed in the cell.
5. Exam-Focused Knowledge
Key Idea 1: The mathematical operators used in spreadsheets are
+, -, *, / and ^.
Key Idea 2: The order of operations follows BIDMAS: Brackets, Indices, Division, Multiplication, Addition and Subtraction.
Key Idea 3: Multiplication and division have equal priority, as do addition and subtraction. Operations with equal priority are evaluated from left to right.
Summary
Mathematical operators allow spreadsheet users to perform calculations using numbers and cell references. The five main operators are addition, subtraction, multiplication, division and exponentiation.
When multiple operators appear in one formula, the spreadsheet follows BIDMAS to determine the calculation order. Understanding these operators and their priority helps students write accurate formulas and obtain correct calculation results.