Testing a formula against a hand-calculated answer

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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

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

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

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

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

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1=SUM(B2:B4) expected 45, actual 45		AThe actual result does not match the expected result, so the formula or its range needs checking
2=AVERAGE(B2:B4) expected 15, actual 12		BThe range stops too early and leaves out the last two rows, so the actual result is lower than the expected result.
3Test a formula on a few rows first, so you can easily work out the expected answer by hand		CThe actual result matches the hand-calculated expected result, so the formula works for this test data
4Check the range covers all the data once the formula passes the small test		D=SUM(B2:B4)
5=SUM(B2:B29) when the data runs to B31		EThe expected result must be worked out by hand before looking at the formula's result, otherwise nothing is really being tested.
6Expected result = whatever the formula shows		F=SUM(B2:B31)

Definitions

expected result

Fill in the Blanks

Complete each line of code by filling in the missing parts.

Expected: 4+6+8 = 18 Actual: =___(B2:B4)

Expected: (4+6+8)/3 = 6 Actual: =_______(B2:B4)

Data is in rows 2 to 10 =SUM(B2:B__)

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

Expected highest of 4, 9, 7 is 9 =___(B2:B4)

Spot the Error

Shade in the letter of the line that contains an 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

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

Question 3

A Data is in B2:B21 (20 rows)

B Formula: =SUM(B2:B11)

C Check: range covers all rows

Question 4

A Test with first 3 rows only

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

C Formula: =MAX(B2:B4)

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

Answer Key

Testing a formula against a hand-calculated answer

Section A: Quick Questions

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

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

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

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

A — Whether the range C2:C5 covers all of the data

Section B: 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.

Section C: Definitions

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.

Section D: Fill in the Blanks

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

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

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

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

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

Section E: Spot the Error

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

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

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

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

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