Spreadsheet Mathematics: The =SUM() Formula and Budgets

Grade 6 (Primary 6) Term 1 • Week 8

About 35 minutes

Learning goals: Explain what a formula is and why every formula in a spreadsheet MUST begin with an equals sign ('='). • Use the standard arithmetic operators in spreadsheets: `+` (addition), `-` (subtraction), `*` (multiplication), and `/` (division). • Write the `=SUM()` formula using cell ranges (e.g., `=SUM(B2:B5)`) to calculate totals automatically. • Build a simple class budget table that recalculates totals instantly when numbers change.

In Week 7, you learned how spreadsheets organize data into rows, columns, and cells. You can enter labels for names and values for prices.

Now comes the real magic of computing: automatic calculations! If you have a budget list of thirty school supplies, you do not need to pull out a pocket calculator or add them up on scratch paper. A spreadsheet can add a thousand numbers in less than one millisecond—and if you change the price of one item, the total updates itself automatically!

In this lesson, we will learn the golden rule of Formulas, master the =SUM() function, and calculate our very first school event budget.

Learning Outcomes

By the end of this lesson, you will be able to:

State the golden rule of formulas: Every formula begins with an equals sign (=).
Use computer math operators: + (add), - (subtract), * (multiply), and / (divide).
Write cell range formulas using the colon syntax (e.g., B2:B6).
Build a functioning budget sheet using the =SUM() formula.

Key Vocabulary

Term	Everyday Meaning
Formula	A mathematical expression typed into a cell that calculates a result from other numbers or cells.
Equals Sign (`=`)	The critical starting symbol that tells the spreadsheet: "Calculate this math!"
Function	A pre-programmed mathematical shortcut built into the software (e.g., `SUM`, `AVERAGE`).
Cell Range	A group of adjacent cells selected together, written with a colon between the start and end (e.g., `B2:B5`).
Automatic Recalculation	The ability of a spreadsheet to instantly update answers whenever any input value is edited.

Study Notes: How Formulas Work

THE GOLDEN FORMULA RULE:
Type:     =B2+B3+B4
Press:    [ Enter ]
Display:  Shows the calculated total! (The formula hides in the Formula Bar)

THE ARITHMETIC OPERATORS IN COMPUTING:
Addition:        +   (e.g., =A1 + B1)
Subtraction:     -   (e.g., =A1 - B1)
Multiplication:  *   (e.g., =A1 * B1)  <-- Notice: Asterisk, not 'x'!
Division:        /   (e.g., =A1 / B1)  <-- Notice: Forward slash, not '÷'!

1. The Equals Sign (`=`) is Mandatory!

If you type 500 + 300 into a cell and press Enter, the spreadsheet simply displays the text: 500 + 300. It treats it as a label!
However, if you type =500 + 300, the equals sign commands the computer: "Solve this equation!" The cell instantly displays: 800.

2. The Power of Cell References

Instead of typing raw numbers into formulas, professional coders and accountants use Cell References: - Poor Formula: =500 + 750 + 200
(If the price changes tomorrow, this formula is wrong!) - Champion Formula: =B2 + B3 + B4
(Now, whatever numbers are inside cells B2, B3, and B4 are added together automatically!)

3. The `=SUM()` Function and Cell Ranges

What if you have fifty numbers to add in Column B from Row 2 to Row 51? Typing =B2+B3+B4+B5...+B51 would take all morning!
Instead, we use the built-in =SUM() function with a Cell Range:

$$\mathbf{=SUM(B2:B5)}$$

The colon (:) means "through to".
This formula tells the spreadsheet: "Add every cell starting from B2 all the way down through B5."

Worked Example: Building a Primary 6 Graduation Budget

Look at the spreadsheet below:

+---+--------------------+--------------------+
|   |         A          |         B          |
+---+--------------------+--------------------+
| 1 | Graduation Expense | Cost in Naira      |
+---+--------------------+--------------------+
| 2 | Fruit Drinks       | 12000              |
+---+--------------------+--------------------+
| 3 | Meat Pies & Snacks | 18000              |
+---+--------------------+--------------------+
| 4 | Balloons & Banners | 5000               |
+---+--------------------+--------------------+
| 5 | Certificates       | 7000               |
+---+--------------------+--------------------+
| 6 | Total Budget       | =SUM(B2:B5)        |
+---+--------------------+--------------------+

In cell B6, the student types: =SUM(B2:B5) and presses Enter.
The computer calculates: $12000 + 18000 + 5000 + 7000 = \mathbf{42000}$.
The Test of Power: The teacher announces that a sponsor donated all the fruit drinks for free! The student changes cell B2 from 12000 to 0.
Instantly, without anyone retyping the formula, cell B6 changes to 30000!

Practical Class Activity: The 4-Item Market Calculator

Take a sheet of paper or open a blank spreadsheet on your computer: 1. Recreate the table above with 4 school supplies of your own choice. 2. Enter prices in cells B2, B3, B4, and B5. 3. In cell B6, write the formula: =SUM(B2:B5). 4. Calculate the sum. Then, change the value in cell B3 to a different number and calculate the new total.

Common Mistakes & Corrections

Common Mistake	Why It Is Incorrect	Proper Correction
"Forgetting to type the equals sign '=' at the start."	The spreadsheet treats your formula as a plain text word instead of calculating it.	Always start every calculation with `=`.
"Using the letter 'x' for multiplication: '=B2 x B3'."	The computer thinks 'x' is a variable or cell letter, causing an error.	Use the asterisk symbol: *`=B2 B3`**.
"Typing a semicolon instead of a colon in a range: '=SUM(B2;B5)'."	A colon (`:`) specifies a contiguous range from start to finish.	Use a colon: `=SUM(B2:B5)`.

Notebook Summary

Copy Into Your Computing Notebook

Every calculation formula in a spreadsheet must begin with an equals sign (=).
Use the asterisk (*) for multiplication and the forward slash (/) for division.
The formula =SUM(B2:B5) adds all numbers in the cell range from B2 through B5.
When any number in the range changes, the formula recalculates the total automatically!