Spreadsheets stop being simple calculators the moment you combine more than one operation in a single formula. Today you will build multi-operator formulas that respect BODMAS, force the order you actually mean with parentheses, and answer a real question from your Something Real project workbook (your multi-sheet or polished Unit 2 workbook in {{code:09_specialism/sm3/}}, or whichever project sheet your teacher names).
Without a calculator, what do you think {{code:=10+5*2}} gives: 30 or 20? Keep your answer in your head. You will check it in a few minutes on a real sheet.
Three ideas drive every multi-step formula you write today. Your teacher will prove them live with {{code:=10+5*2}} versus {{code:=(10+5)*2}}, then you will type the three worked problems on a practice sheet.
| Concept | Why it matters | Example |
|---|---|---|
| Arithmetic operators — the five symbols spreadsheets use for maths: {{code:+}} add, {{code:-}} subtract, {{code:*}} multiply, {{code:/}} divide, {{code:^}} raise to a power | Almost every project question (profit, cost per person, growth, remaining budget) needs more than one of these in the same cell | Aoife's pin stall: {{code:=40*3.5}} multiplies units by price in euro |
| BODMAS — the fixed order spreadsheets follow: Brackets, Orders (powers), Division and Multiplication (left to right), Addition and Subtraction (left to right). You might know this as BOMDAS or PEDMAS — same idea, different letters; spreadsheets still do brackets and powers first. | If you ignore BODMAS, a formula can look right and still give the wrong answer, which quietly ruins a budget or a forecast | {{code:=10+5*2}} returns 20, not 30, because multiplication runs before addition |
| Parentheses (brackets) — {{code:()}} force the spreadsheet to finish the inside first, overriding the default BODMAS order | When the real-world meaning is "do this group first", brackets are the only safe way to say so | {{code:=(240-60)/4}} splits the leftover after a €60 deposit between four volunteers; without brackets you get a very different number |
Results below are what you should see after pressing Enter. Powers ({{code:^}}) and multi-operator patterns show up inside these formulas — watch the order, not just the final number.
| Problem | Formula | Result |
|---|---|---|
| 1. Profit: 40 pins at €3.50; materials €45; postage €18 | {{code:=40*3.5-(45+18)}} | 77 |
| 2. Float each: €240 left, pay €60 deposit, split the rest between 4 people | {{code:=(240-60)/4}} | 45 |
| 3. Reach estimate: start at 80, triple across 2 cycles | {{code:=80*3^2}} | 720 |
With a partner, build a short practice sheet that proves BODMAS, solve three worded problems with multi-operator formulas, then rebuild the profit formula from input cells so the result updates when a number changes. Your teacher will confirm the portfolio folder path, demo the first formula on the classroom screen, and may project the label scaffold so you can focus on the formulas in column B.
Wrong answers are normal here — BODMAS catches almost everyone once. Use this table before you rebuild the formula.
| Issue | Solution |
|---|---|
| My formula shows the text of the formula instead of a number | Check the cell does not start with a space or an apostrophe before the equals sign. Delete the cell contents and retype starting with {{code:=}}. |
| I get a different answer from my partner on Problem 2 | Compare brackets. {{code:=240-60/4}} and {{code:=(240-60)/4}} are different formulas. BODMAS divides before it subtracts unless brackets force the subtraction first. If your pair disagrees, type both versions side by side; the gap is the lesson. |
| I see {{code:#DIV/0!}} or {{code:#VALUE!}} | {{code:#DIV/0!}} means something is dividing by zero (check the divisor cell). {{code:#VALUE!}} usually means a label or word landed inside a maths formula — point the formula at number cells only. |
| Power formula looks wrong (for example {{code:80*3^2}} not giving 720) | Use the caret {{code:^}} for powers, not the letter x. Confirm you typed {{code:3^2}} and not {{code:32}} or {{code:3*2}}. Orders run before multiplication, so {{code:80*3^2}} is 80×9. |
| My cell-input profit did not change when I edited units | Check the formula uses cell references such as {{code:B11}} and {{code:B12}}, not typed numbers. If you still see the old total, click the profit cell and confirm the formula bar shows references, then press {{key:Enter}} again. |
Independent practice uses the same pattern you just built with input cells. Your teacher will walk this mini-model once on the board (or on a sample project tab) before you open your own workbook.
| Cell | Contents | Role |
|---|---|---|
| {{code:A1}} | Question: What profit remains after materials and postage? | Plain-English project question |
| {{code:A3}} / {{code:B3}} | Units / 40 | Input you can change |
| {{code:A4}} / {{code:B4}} | Price (euro) / 3.5 | Input |
| {{code:A5}} / {{code:B5}} | Materials (euro) / 45 | Input |
| {{code:A6}} / {{code:B6}} | Postage (euro) / 18 | Input |
| {{code:A8}} / {{code:B8}} | Profit (euro) / {{code:=B3*B4-(B5+B6)}} | Multi-operator formula with brackets |
| {{code:A9}} | Note: Profit after two costs for the pin stall | One-line explanation for your Key Assignment evidence |
Change units from 40 to 50 and watch profit update. That live update is what "stays correct when the inputs change" means.
You're previewing this lesson. Get full access to this lesson and hundreds more — each one ready to teach, with interactive activities, printable resources and pupil progress tracking built in.