Did you know that a spreadsheet can label dozens of school results incorrectly even when its formula looks perfectly tidy? One symbol is enough. A rule that says ‘eight or more’ is not the same as a rule that says ‘more than eight’. If nobody checks the exact boundary, a spreadsheet may confidently apply the wrong decision to every row.
A boundary-value test checks what a rule does at the threshold itself, just below it and just above it. For an Excel IF formula, write the rule in plain English, choose the matching comparison operator, then test these three cases before filling the formula down a long column. Add tests for blank, invalid or unusual inputs when those can occur. This is an efficient way to catch a logic error before it becomes a report.
The meaning of IF: one test, two possible branches
Excel’s IF function follows the structure IF(logical_test, value_if_true, value_if_false): it evaluates a logical test and returns the value for the appropriate branch. The official Microsoft guidance on conditional formulas confirms the role of the logical test, the true result and the false result. A formula can be syntactically valid yet implement a rule different from the one a person intended.
The four comparisons pupils should be able to explain are > (greater than), >= (greater than or equal to), < (less than), and <= (less than or equal to). In actual Excel formula text, the operators are entered using keyboard characters, as shown. The equals sign at the start of a formula tells the spreadsheet that a calculation follows; an equals sign inside a comparison can be part of the logical test.
Worked example: a fictional school library training checklist
Imagine a library volunteering activity with twelve practice tasks. For this invented teaching exercise only, the activity leader has decided that a pupil who completes at least eight tasks receives the label ‘Ready’, while someone completing fewer than eight receives the label ‘Practise’. This is not an actual school’s qualification or grading policy.
Put the number of completed tasks in cell B2. A clear starting formula for cell C2 is =IF(B2>=8,"Ready","Practise"). The test B2>=8 is true when B2 is eight, nine, ten, eleven or twelve. It is false when B2 is seven or fewer. The boundary word at least is why the equals sign belongs after the greater-than sign.
- B2 = 7: 7 >= 8 is FALSE, so the result is ‘Practise’.
- B2 = 8: 8 >= 8 is TRUE, so the result is ‘Ready’.
- B2 = 9: 9 >= 8 is TRUE, so the result is ‘Ready’.
- B2 = 0: 0 >= 8 is FALSE, so the result is ‘Practise’ if zero is a recorded score.
- B2 = 12: the formula returns ‘Ready’, within the twelve-task maximum.
The formula =IF(B2>8,"Ready","Practise") is different: B2 = 8 now returns ‘Practise’. That is an off-by-one boundary mistake. It may stay invisible in a screenshot showing only scores of five and ten. The three-case boundary test finds it immediately.
Why a blank input should not silently become a real result
In an ordinary worksheet, a blank task-count cell means something different from a recorded zero. Zero means that no tasks were completed; blank may mean the record has not been entered or checked. The basic IF formula does not fully express that distinction, because a blank input need not produce a separate ‘Not recorded’ label. The safer classroom example is =IF(B2="","Not recorded",IF(B2>=8,"Ready","Practise")). This checks for blank first, and only then tests the threshold.
Be precise about the spreadsheet’s behaviour. Blank cells, empty-string formula results, text entries and imported data can behave differently in edge cases. The suggested example is for a simple manually maintained Excel worksheet whose input cell is expected to hold a numeric count or be genuinely blank. Do not promise that one formula handles every malformed value or every spreadsheet program without testing it.
What if a pupil types thirteen tasks when the maximum is twelve?
The original threshold formula would label 13 as ‘Ready’. But thirteen of twelve tasks is not a valid count for this scenario. The issue is now input validation rather than threshold logic. A teacher could use a separate entry check or suitable data validation, or extend the formula deliberately: =IF(B2="","Not recorded",IF(OR(B2<0,B2>12),"Check entry",IF(B2>=8,"Ready","Practise"))). That version is a worked illustration rather than a universal data-cleaning solution; other text or error values still need attention.
The goal is not to dazzle beginners with nested formulas. It is to show why a correct threshold rule and a valid input are two separate requirements. A well-designed sheet records missing or invalid data honestly, rather than labelling it as achievement or failure by accident.
A small truth table catches errors faster than checking twenty random rows
- Write the intended rule: at least eight of twelve valid tasks.
- Select the critical values: 7, 8 and 9.
- Record expected outputs before opening the spreadsheet: Practise, Ready, Ready.
- Compare the calculated outputs with those expectations.
- Add a blank input and an impossible value such as 13; decide whether the sheet should say ‘Not recorded’ or ‘Check entry’.
- Only after those checks pass should the formula be copied to the other rows.
This is more effective than testing only typical examples. A value of 3 and a value of 11 exercise opposite branches, but they do not reveal whether the pupil used > instead of >= at the threshold. Boundary tests are chosen because they distinguish competing rules.
A diagnostic for students: can they translate the sentence into an operator?
Ask: ‘At least eight tasks’ means which operator? Answer: >= 8. ‘More than eight tasks’ means > 8. ‘No more than eight tasks’ means <= 8. 'Fewer than eight tasks' means < 8. Ask the learner to explain what happens at exactly eight for each rule. If they can state the operators but cannot explain the English phrases, practise language-to-logic translation before adding complexity.
If the child answers the language questions correctly but the formula produces unexpected labels, check typing and cell references. A student who writes >= correctly in a notebook may accidentally refer to A2 rather than B2. That is a different error. The same independent testing habit helps diagnose it.
How copying a correct formula can still introduce a problem
Suppose B2 contains the first pupil’s task count and C2 contains the IF formula. When copied down appropriately, a relative reference B2 normally becomes B3, B4 and so on. That is desirable for one score per row. But if the sheet has been sorted incorrectly or a formula refers to a fixed cell that should vary, the output can describe the wrong pupil.
Check one early, one middle and one final row after filling down. Confirm each result follows that row’s count. Do not infer that the entire range is correct merely because the top cell worked. In a small practice exercise, hand-calculate several results independently and compare them with the spreadsheet.
A complete independent practice challenge
Invent a second rule: a fictional after-school art club lends a specialist tool only when a student’s recorded safety checklist score is no more than two errors, with scores between zero and ten. Which operator expresses the rule? The answer is <= 2. One starting formula for a valid numeric entry in D2 is =IF(D2<=2,"Eligible","Review"). At D2 = 1 it returns Eligible; at D2 = 2 it still returns Eligible; at D2 = 3 it returns Review. The rule is a classroom logic exercise, not a real workshop safety policy.
Now change the policy phrase to ‘fewer than two errors’. The correct test becomes D2<2, and a score of exactly two returns Review rather than Eligible. Ask the pupil to explain why the new rule changes just the boundary case and not every number. This isolates the threshold reasoning from copying a memorised spreadsheet pattern.
Progression from guided to independent work
- Stage 1: Sort written phrases into >, >=, < and <= without a computer.
- Stage 2: Predict the three outputs around one given threshold.
- Stage 3: Type one simple IF formula and check it using a truth table.
- Stage 4: Distinguish a blank from a recorded zero and discuss invalid inputs.
- Stage 5: Fill a short sheet down, then independently verify which row each formula reads.
- Stage 6: Given an unfamiliar rule, design tests before writing the formula.
A useful delay check is to return a few days later with a different threshold, such as 'at most four absences', and ask the learner to design tests unaided. Memorising the word IF is not the aim. Translating a rule into a checkable decision is the transferable skill.
What parents and tutors should observe
A tutor can ask the learner to explain why eight belongs in the true branch, not just whether the formula runs without errors. A parent can read a simple rule aloud and invite the child to predict results for one below, equal to and one above the threshold. No family needs to share real class marks, private pupil details or school spreadsheets for this practice.
Track the number of independently chosen boundary tests and corrected reference mistakes over two or three new exercises. Faster clicking is not the same as better understanding. The strongest progress signal is that the child checks a formula before trusting the row labels it produces.
Limitations: spreadsheets do exactly the rule we express, not necessarily the rule we meant
The examples assume numeric inputs and specified valid ranges. Real files may contain text, imported values, locale-specific separators, error cells and rules more complicated than one threshold. Test on a duplicate practice sheet and seek guidance before using a spreadsheet output to make consequential decisions about real students.
Different spreadsheet applications and locale settings may use different argument separators or formatting. Consult the documentation for the actual program, and keep the underlying rule visible in plain language. No formula should secretly create school requirements that have not been authorised.
Frequently asked questions
Why do I test just below, at and above the threshold?
Those three values reveal whether the comparison operator includes or excludes the exact boundary.
Is >= the same as >?
No. >= includes equality; > excludes the exact threshold value.
Is a blank cell the same as zero?
Not conceptually. Blank often means not recorded, while zero can be a valid recorded count. Check your data-entry design.
Does Excel guarantee the rule is fair?
No. It follows the written formula. A clear formula cannot validate whether the underlying policy is appropriate or correct.
How many rows should I test after copying down?
Test boundary cases and representative rows across the range. For important uses, have an independent review or automated validation appropriate to the risk.
Related learning at eduKate
Read how to sort a school spreadsheet without separating labels from values for the related row-alignment risk. The spreadsheet formulas and references guide extends the skill. Visit the eduKate Education Hub and the Clementi Mathematics teaching reference for learning built on diagnosis, explanation and independent checking.
The habit to keep is simple: before trusting a neat column of labels, test the row whose value lies exactly on the boundary. A single carefully chosen example can expose a misunderstanding that dozens of ordinary values conceal.