Simple Spreadsheet Formulas and Functions

Lesson objectives

By the end of the week, learners should be able to:

1. Use the SUM function to add a range of cells.
2. Use the AVERAGE function to compute a mean score.
3. Apply basic arithmetic operators such as addition, subtraction, multiplication and division.
4. Reference a cell by its letter and number.
5. Validate a calculated total against a hand-computed value.

Key vocabulary

• Function: a built-in formula that performs a calculation automatically.
• SUM: the function that adds a range of numeric cells.
• AVERAGE: the function that returns the mean of a range.
• Arithmetic operator: a symbol such as plus, minus, multiply or divide.
• Cell reference: the address such as A1 or B2 that points to a cell.
• Range: a group of cells identified by start and end references.
• Validation: checking a computed result against a known value.

Common misconceptions

• "Spreadsheets do everything themselves." They store the data you put in; functions help calculations, not decisions.
• "SUM always includes the header row." SUM must point only at numeric cells to avoid counting text values.
• "Each function does one thing alone." Most functions combine arguments inside a single formula.

Teacher activities

1. Starter: show two sums, one correct and one incorrect, and ask learners to find the mistake.
2. Name SUM and AVERAGE and give one example each on the board.
3. Demonstrate a full sum formula referencing A1 through A10 and compare with manual addition.
4. Show the average formula and round the result to two decimal places.
5. Demonstrate arithmetic operators on sample cells and confirm with hand calculations.
6. Run the validation task: compute the sum by hand and compare it with the spreadsheet result.

Student activities

1. Spot the SUM error in the starter display and explain the fix.
2. Enter the SUM formula for a numeric range.
3. Enter the AVERAGE formula and round the result.
4. Perform an arithmetic operation using at least two operators.
5. Reference a named cell and describe what the reference does.
6. Complete the validation task and record the matched or mismatched value.

Practical / learning evidence

A worksheet workbook with the SUM and AVERAGE functions producing correct answers, plus the hand-computed reference values side by side. Save both comparisons and inspect the workbook.

Resources and preparation

Computers with Excel or Calc, sample scores data and a hand-computation worksheet. Disable Caps Lock before drills. Pre-fill the hand-computed values so learners can validate efficiently.

Assessment for learning

Check the workbook functions for correct ranges and results. Compare the sheet result with the hand-computed value and flag mismatches. Ask a learner to describe what happens if SUM includes a text header rather than a number.

Inclusion and homework

Provide the sample data and hand-computed values already filled in so learners focus on formula placement. Allow extra time and provide a numbered steps reference for learners who lose track. Extend confident learners with COUNTIF and IF.