Spreadsheet Basics
Senior 2 · ICT
Spreadsheet Basics
Lesson 4 of 5
18 min
Let's begin
At the school shop, pens cost 350 Frw, exercise books 600 Frw and rulers 250 Frw. The shopkeeper wants the spreadsheet to multiply each price by the quantity sold and add up the day's takings, and to update everything the moment a price changes. That is what formulas do.
A formula is an instruction that calculates a value. In Calc and Excel, every formula starts with an equals sign, =. Without it, the entry is treated as text.
The symbols that tell the spreadsheet which calculation to do are the arithmetic operators:
| Operator | Meaning | Example | Result |
|---|---|---|---|
| + | Add | =7 + 5 | 12 |
| - | Subtract | =7-5 | 2 |
| * | Multiply | =7*5 | 35 |
| / | Divide | =A1/B1 with A1 = 12 and B1 = 5 | 2.4 |
| ^ | Power (exponent) | =7^2 | 49 |
| % | Percent (divides by 100) | =150*20% | 30 |
When a formula has several operators, the spreadsheet works in this order, like BODMAS in Mathematics:
Each line is a formula exactly as you would type it into a cell, followed by the result the cell shows. Type them into Calc or Excel to check.
A formula can refer to other cells by their references. Then, if a value changes, the result updates automatically.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Price (Frw) | Quantity | Amount (Frw) |
| 2 | Pen | 350 | 4 | =B2*C2 → 1,400 |
| 3 | Exercise book | 600 | 3 | =B3*C3 → 1,800 |
| 4 | Ruler | 250 | 2 | =B4*C4 → 500 |
| 5 | Total | =D2+D3+D4 → 3,700 |
Tip
Start with =, finish with brackets in mind. If you want addition to happen before multiplication, you must put it in brackets.
Cell A1 holds 12 and cell B1 holds 5. Work out each formula before checking.
=A1+B1*2
Solution: Multiplication first: 5 × 2 = 10, then 12 + 10 = 22.
=(A1+B1)*2
Solution: Brackets first: 12 + 5 = 17, then 17 × 2 = 34.
=A1^2
Solution: 12 × 12 = 144.
=A1-B1*2 + 3
Solution: Multiplication first: 5 × 2 = 10. Then left to right: 12 − 10 = 2, and 2 + 3 = 5.
Cell C1 is empty. What does =A1/C1 show, and why?
Solution: #DIV/0!, because an empty cell counts as zero and you cannot divide by zero.
Remember
Sources and credits
=2+3*4 gives 14 (3*4 = 12 first, then 2 + 12)
=(2+3)*4 gives 20 (brackets first: 5, then 5 * 4)
=10-4/2 gives 8 (4/2 = 2 first, then 10 - 2)
=2^3*2 gives 16 (2^3 = 8 first, then 8 * 2)
=(8+4)/2^2 gives 3 (12, then 2^2 = 4, then 12 / 4)