Testing a model means choosing input values on purpose, working out the answer by hand, and comparing it with what the sheet shows. The values that matter most are those at the boundary of a rule, where the result switches.
This lesson follows formatting units in the module on spreadsheet modelling and charts, and uses the layout from separating inputs, calculations and outputs.
What should you test?
For every condition in the model, test three kinds of value:
- Just below the boundary.
- Exactly at the boundary.
- Just above the boundary.
Also test an empty or zero input where the model divides, and an invalid input where the rule should reject it.
How to run a test, step by step
- List the rules in the model, such as thresholds and divisions.
- Write a test table with the input values and the expected result for each, calculated by hand.
- Enter each value into the input cell, one at a time.
- Compare the sheet’s result with your expected result.
- Fix any mismatch, then run the whole table again, because a fix can break another case.
Worked example
An invented online shop charges RM8 delivery unless the order total is RM150 or more. The total is in B2, and B3 holds:
=IF(B2>=150,0,8)
| Test value in B2 | Expected delivery | Sheet result |
|---|---|---|
| 149.99 | 8 | 8 |
| 150 | 0 | 0 |
| 150.01 | 0 | 0 |
| 0 | 8 (or reject, if the task says so) | 8 |
All rows match, so the rule is correct. The point is that 150 itself was tested, because “RM150 or more” includes it.
Division test: profit margin in B6 is =B4/B5 where B5 is revenue. If B5 is 0, the sheet shows an error. The safer form is =IF(B5=0,0,B4/B5). With B4 = 156 and B5 = 300 it gives 0.52, and with B5 = 0 it gives 0.
The mistake to watch for
A common slip is to use the wrong comparison, then test only easy values.
Mistaken formula: =IF(B2>150,0,8)
Tested with 200 and 50, it gives 0 and 8, which look right. But an order of exactly 150 pays RM8, which breaks the rule.
The correction is to use >= and to include the boundary in the test table. Testing only typical values passes the faulty formula. The boundary test is the one that fails it.
Check yourself
1. The formula =IF(A1>=40,“Pass”,“Fail”) is meant to pass scores of 40 and above. What should it show for 39, 40 and 41?
Show answer
Fail, Pass, Pass. The boundary 40 passes because the rule uses >=.
2. A rule gives a student discount to ages under 18. The formula is =IF(B2<18,0.2,0). What discount does an 18-year-old get, and what do ages 17 and 19 get?
Show answer
Age 18 gets 0, age 17 gets 0.2 (20%), and age 19 gets 0. Age 18 is not under 18, so the boundary is outside the discount.
3. A cell holds =B4/B5 and B5 can be 0. Write a safer version and state its result when B4 = 50 and B5 = 0.
Show answer
=IF(B5=0,0,B4/B5). With B5 = 0 it returns 0, so no error appears.
Where this leads next
The last step is to check what the reader actually receives: check print areas and hidden rows. The read-only SQL practice lab applies the same boundary thinking to database conditions.
When tests keep passing but the marker’s values fail, a teacher in online one-to-one ICT tuition can help you build a test table before you start on the formulas.