← VCE Spreadsheets, SQL & Visualisation

Testing a formula against a hand-calculated answer

The answer you work out by hand using a few rows of data. Compare it with the formula's actual result. If they match, the formula works. If not, check the formula and its range.

Part of: VCE Applied Computing - U1O1 - Data analysis — Module 33, VCE Data Analytics - U3O1 - Data Analytics — Module 144, VCE Data Analytics - U4O1 - Development and Evaluation — Module 88, ACARA Digital Technologies Years 9–10 — Acquiring and Validating Data — Module 110

Understanding Testing a formula against a hand-calculated answer in VCE Spreadsheets, SQL & Visualisation

What it does: A small range of three rows is quick to add up by hand, so the actual result can be compared with the expected result. After the small test works, the range is extended to every data row (B2 to B31) so no values are missed. When the results do not match, check the formula and its range. A range that stops too early leaves out values, so the total is too low. A mismatch between the hand-calculated answer and the spreadsheet result is a sign of an error to investigate.

Common mistakes: The hand calculation is wrong. 4+6+5 equals 15, so the expected result should be 15. The expected result is 4 and the actual result is 4, so they match. The line should say 'so a match'.

Class: _____________________ · Name: _____________________ · Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Testing a formula against a hand-calculated answer to a class →

This module's key terms are also in a printable crossword (Modules 21–34) and word search (Modules 4–34).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning

Duo

Which is the best way to test that =SUM(B2:B6) works?

Check that the formula returns a number, not an errorAdd the five numbers by hand, then compare with the cell's result

Matches

=SUM(B2:B29) when the data runs to B31

↓

The range stops too early and leaves out the last two rows, so the actual result is lower than the expected result.

Applying

Meanings

Term

expected result

Meaning

The answer you work out by hand using a few rows of data. Compare it with the formula's actual result. If they match, the formula works. If not, check the formula and its range.

Practice

=12+15+9

Testing

Gaps

Actual: =(B2:B4)

Check Answer

Spots

1Data in B2:B4: 4, 6, 5

2Expected: 4+6+5 = 14

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy

Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games

Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print

Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. Which is the best way to test that =SUM(B2:B6) works?

A. Check that the formula returns a number, not an error

B. Add the five numbers by hand, then compare with the cell's result ✓

A formula can return a number and still be wrong. Work out the expected result by hand and compare it with the actual result.

2. What is the expected result?

A. The number the spreadsheet shows after you enter the formula

B. The answer you work out by hand before checking the spreadsheet ✓

The number the spreadsheet shows is the actual result. The expected result is the hand-calculated answer you compare it with.

3. B2:B4 holds 10, 20 and 30. You enter =AVERAGE(B2:B4) and the cell shows 20. What does this tell you?

A. The actual result matches the expected result of 20, so the formula works ✓

B. The formula is wrong because the expected result is 60

By hand, (10 + 20 + 30) / 3 = 20. The expected and actual results match, so the formula passes this test.

4. Which approach is better when you first test a new formula?

A. Try it on a few rows of data first, where you can easily add up the answer by hand ✓

B. Try it on all 500 rows at once so every value is checked together

A small set of data makes the hand calculation quick and easy. Once the formula passes, you can use it on the full data.

5. Your hand-calculated total is 85, but =SUM(C2:C5) shows 60. What should you check first?

A. Whether the range C2:C5 covers all of the data ✓

B. Whether the hand-calculated answer should be changed to 60

When the results do not match, check the formula and its range. A range that stops too early leaves out values, so the total is too low.

Match the Pairs

=SUM(B2:B4) expected 45, actual 45 → The actual result matches the hand-calculated expected result, so the formula works for this test data

=AVERAGE(B2:B4) expected 15, actual 12 → The actual result does not match the expected result, so the formula or its range needs checking

Test a formula on a few rows first, so you can easily work out the expected answer by hand → =SUM(B2:B4)

Check the range covers all the data once the formula passes the small test → =SUM(B2:B31)

=SUM(B2:B29) when the data runs to B31 → The range stops too early and leaves out the last two rows, so the actual result is lower than the expected result.

Expected result = whatever the formula shows → The expected result must be worked out by hand before looking at the formula's result, otherwise nothing is really being tested.

Key Term

expected result

The answer you work out by hand using a few rows of data. Compare it with the formula's actual result. If they match, the formula works. If not, check the formula and its range.

Fill in the Blanks

1. Expected: 4+6+8 = 18 Actual: =SUM(B2:B4)

Hint: Type the function that adds a range of values

2. Expected: (4+6+8)/3 = 6 Actual: =AVERAGE(B2:B4)

Hint: Type the function that finds the mean of a range

3. Data is in rows 2 to 10 =SUM(B2:B10)

Hint: Type the row number of the last data row so the range covers everything

4. B9 is expected, B10 is actual =IF(B9=B10,"Match","Check")

Hint: Type the operator that tests whether two values are equal

5. Expected highest of 4, 9, 7 is 9 =MAX(B2:B4)

Hint: Type the function that returns the largest value in a range

Spot the Error

Question 1

A Data in B2:B4: 4, 6, 5

B Expected: 4+6+5 = 14

C Formula: =SUM(B2:B4)

D Actual result: 15

E Expected = actual, so correct

Line B — The hand calculation is wrong. 4+6+5 equals 15, so the expected result should be 15.

Question 2

A Test rows: 2, 4, 6

B Expected mean: 12 / 3 = 4

C Formula: =AVERAGE(B2:B4)

D Actual result: 4, so no match

E Conclusion: formula works

Line D — The expected result is 4 and the actual result is 4, so they match. The line should say 'so a match'.

Question 3

A Data is in B2:B21 (20 rows)

B Formula: =SUM(B2:B11)

C Check: range covers all rows

Line B — The range stops at row 11, so it misses half the data. It should be B2:B21 to cover all 20 rows.

Question 4

A Test with first 3 rows only

B Expected: MAX of 7, 9, 3 = 7

C Formula: =MAX(B2:B4)

Line B — The largest of 7, 9 and 3 is 9, not 7. The hand-calculated expected result should be 9.

Question 5

A Data B2:B5: Yes, No, Yes, Yes

B Expected count of Yes: 3

C =COUNTIF(B2:B4,"Yes")

D Actual result: 3

Line C — The range B2:B4 misses the last row, so the actual result would be 2, not 3. The range should be B2:B5.

Student with a class code? Join your class →