1 - Introduction ~5 mins
Welcome
Your Something Real budget already has real costs in it. Today you stop adding those costs by hand. You will write two formulas, SUM and AVERAGE, so totals and averages recalculate the moment a price changes. That is the difference between a static list and a working tool you can trust for decisions.
| What changes | Hand-added total | Formula total |
|---|---|---|
| Instagram ads go from €20 to €50 | You re-add every row and hope you do not miss one | Total jumps from €158.50 to €188.50 on its own |
| Average cost of five items | You divide again by hand | Average moves from €31.70 to €37.70 automatically |
If you do not have {{code:05_project_budget}} yet, tell the teacher and use the phone-case practice sheet for today's formulas, then copy them across when your budget is ready.
By the end of this lesson, you will be able to:
- Write formulas that start with equals and use cell references rather than typed values
- Build SUM and AVERAGE formulas over a range
- Watch results update when source data changes
- Add Total and Average Cost calculations to {{code:05_project_budget}}
Warm-up
Imagine your project has twelve costs. You add them up carefully and get a total. Then one supplier changes a price. How long does it take to redo the total by hand, and how easy is it to miss a row? Keep that annoyance in mind. Formulas exist so you never have to do that again.
2 - Key Concepts ~5 mins
Before you type anything, lock these three ideas. Every formula you write today rests on them. You will meet SUM and AVERAGE in the next activity as you type them.
| Concept | Why it matters | Example |
|---|---|---|
| Formula — an instruction that starts with {{code:=}} and tells the spreadsheet to calculate something | Without the equals sign, the sheet treats your typing as plain text and will not calculate | Typing {{code:=SUM(C2:C6)}} in a total cell; typing {{code:SUM(C2:C6)}} without equals just sits there as words |
| Cell reference — using a cell address (like {{code:C2}}) instead of typing the number that is currently in that cell | When the cost in {{code:C2}} changes, every formula that points at {{code:C2}} updates automatically | In a phone-case side hustle budget, {{code:C2}} holds €28.50 for vinyl wrap; the total uses {{code:C2}}, not the typed number 28.50 |
| Range — a block of cells from one corner to another, written with a colon | One range lets you total a whole cost column without naming every cell | {{code:C2:C6}} means every cost from row 2 down to row 6 |
Quick pair check: If your teacher gives you the Match the formula idea sheet, spend a few minutes matching each idea on the left with what it does. Check answers together before the guided task.
Worked example snapshot
In the guided task you will build this small side-hustle cost list (custom phone cases for a local market stall), then type SUM and AVERAGE underneath to get live results:
| Item | Category | Cost (€) |
|---|---|---|
| Vinyl wrap sheets | Materials | 28.50 |
| Blank cases x20 | Materials | 45.00 |
| Heat press hire | Equipment | 35.00 |
| Instagram ads | Promo | 20.00 |
| Market stall fee | Venue | 30.00 |
| Total | 158.50 via SUM | |
| Average cost | 31.70 via AVERAGE |
Then you will change one price and watch both answers move. That live update is the whole point of formulas.
3 - Step-by-step Task ~18 mins
Build a small practice sheet for a custom phone-case side hustle. Enter five costs, add SUM and AVERAGE with cell references, then change one price and watch both results update. You will use the same pattern on your own {{code:05_project_budget}} next.
4 - Common Issues ~4 mins
Common Issues
| Issue | Solution |
|---|---|
| I typed SUM(C2:C6) but nothing calculated | Every formula must start with equals. Use {{code:=SUM(C2:C6)}}. Without {{code:=}}, the sheet treats it as plain text. |
| My total looks right now, but it does not change when I edit a cost | You probably typed a number instead of a formula. Click the total cell and check the formula bar. If you only see a number, replace it with {{code:=SUM(...)}} over your cost range. |
| The total is too small or too large | Check the range covers every cost row and only cost rows. If costs run from row 2 to row 12, use {{code:C2:C12}}. A short range misses items. A long range is only a problem if it pulls in other numbers (for example a second total or a deposit figure further down). Empty cells on their own do not change SUM. |
| Total shows an error or keeps changing oddly | Your SUM range probably includes the Total cell itself. Stop the range on the last cost row above Total. Never put the Total cell inside its own SUM. |
| I see {{code:#DIV/0!}} on the average | AVERAGE is trying to divide by zero because the range has no numbers yet. Enter numeric costs first, or point AVERAGE at the rows that actually contain costs. |
| AVERAGE looks wrong because a label got included | Make sure the range covers only the numeric cost cells, not the heading row and not a Total label sitting in the same column. |
5 - Independent Practice ~20 mins
Independent Practice
Leave one blank row under your last cost. In column A, type {{code:Total}} on the next row and {{code:Average cost}} on the row below.
Worked transfer: the practice sheet used {{code:C2:C6}} because it had five costs. Your list may be longer. If your costs fill {{code:C2}} to {{code:C11}}, Total uses {{formula:=SUM(C2:C11)}} and Average cost uses {{formula:=AVERAGE(C2:C11)}}. Never include the Total cell itself in the range.
Checklist:
- Click the first Cost value in your list.
- Drag or Shift-click to the last Cost value only. Stop above Total.
- In the Cost column beside Total, type {{code:=SUM(}} that range {{code:)}} and press Enter.
- Beside Average cost, type {{code:=AVERAGE(}} the same range {{code:)}} and press Enter.
- Change one real cost on purpose and confirm both answers move.
- Keep the file named {{code:05_project_budget}} inside {{code:05_budget}}.
- Total and Average cost use formulas (formula bar shows {{code:=SUM(...)}} and {{code:=AVERAGE(...)}}, not plain numbers) over the full Cost column
- Changing one cost updates both Total and Average without retyping anything
- Labels are clear and costs read as money
- {{code:05_project_budget}} is saved in {{code:05_budget}} inside {{code:Project_Portfolio}}
Unlock the full learning experience
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.
This lesson is copyright of Coding Ireland 2017 - 2025. Unauthorised use, copying or distribution is not allowed.