Be able to use arithmetic operators in formulae including add, subtract, multiply, divide, indices

Published by Patrick Mutisya · 14 days ago

Cambridge IGCSE ICT 0417 – Spreadsheets: Arithmetic Operators

Spreadsheets – Using Arithmetic Operators in Formulae

1. Introduction

Spreadsheets allow you to perform calculations automatically by entering formulae into cells. A formula always begins with an equals sign (=) and can contain numbers, cell references, functions and arithmetic operators.

2. The Five Basic Arithmetic Operators

The following operators are used in most spreadsheet packages (e.g., Microsoft Excel, Google Sheets, LibreOffice Calc):

OperatorSymbol in SpreadsheetMathematical EquivalentExample
Addition+\$a + b\$=A1 + B1
Subtraction-\$a - b\$=A1 - B1
Multiplication*\$a \times b\$=A1 * B1
Division/\$\dfrac{a}{b}\$=A1 / B1
Indices (Exponents)^\$a^{b}\$=A1 ^ 2

3. Building a Formula

A typical formula follows this structure:

  1. Start with =.

  2. Enter the first operand (a number, cell reference, or function).

  3. Enter the operator.

  4. Enter the second operand.

  5. Close any parentheses if you are grouping parts of the calculation.

Example: To calculate the total cost of 5 items priced in cell B2 with a tax rate of 7% stored in cell C1:

=5 * B2 * (1 + C1)

4. Order of Operations (PEMDAS)

Spreadsheets follow the standard mathematical order of operations:

  • Parentheses – calculations inside brackets first.

  • Exponents – the ^ operator.

  • Multiplication and Division – from left to right.

  • Addition and Subtraction – from left to right.

If you want to change the natural order, use parentheses to group the desired operations.

5. Example Formulae

Below are common scenarios you may encounter in the IGCSE exam.

TaskFormulaExplanation
Sum of two cells=A2 + B2Adds the values in A2 and B2.
Find the difference between a total and a discount=C5 - D5Subtracts the discount (D5) from the total (C5).
Calculate area of a rectangle (length in A1, width in B1)=A1 * B1Multiplies length by width.
Convert kilometres to miles (1 km = 0.621371 miles)=E2 * 0.621371Multiplies the kilometre value by the conversion factor.
Compound interest for 3 years, rate in C1, principal in B1=B1 * (1 + C1) ^ 3Raises (1 + rate) to the power of 3 and multiplies by the principal.

6. Common Errors to Watch For

  • Forgetting the leading = – the cell will display the text instead of the result.

  • Using the wrong operator symbol (e.g., × instead of *).

  • Division by zero – results in an error (#DIV/0!).

  • Incorrect use of parentheses leading to unexpected results.

  • Mixing text and numbers without converting the text to a numeric value.

7. Practice Questions

  1. In cells A1 and B1 you have the numbers 12 and 4. Write a formula that returns the result of \$12 \div 4 + 3\$.

  2. Cell C2 contains the price of an item (£25). Cell D2 contains the \cdot AT rate (20%). Write a formula to calculate the price including \cdot AT.

  3. Using cells E3 (base) and F3 (exponent), write a formula that computes \$E3^{F3}\$.

  4. Calculate the total cost of 8 items where the unit price is in G5 and a discount of 15% is stored in H5. Show the formula.

  5. Explain why the formula =A1 ^ B1 * C1 might give a different result from =A1 ^ (B1 * C1) when A1=2, B1=3, C1=2.

8. Summary

Mastering arithmetic operators in spreadsheet formulae enables you to perform a wide range of calculations quickly and accurately. Remember the symbols +, -, *, / and ^, respect the order of operations, and always use parentheses to control the sequence of evaluation.

Suggested diagram: Flowchart showing the order of operations (PEMDAS) as applied in a spreadsheet.