Apply explicit decision rules

Translate a written rule containing "at least" and "and" into a formula, test it on either side of its boundary and at the value it was never given, and keep a missing input as a third outcome rather than a refusal.

A policy is written in English and applied in a spreadsheet, and the translation is where the money goes missing.

"At least twelve" becomes > instead of >= and every session with exactly twelve people is quietly refused. Nobody complains, because nobody knows: the sessions that lost out are not on a list anywhere. "Attendance of twelve and a partner site" becomes OR and the stipend goes to sessions that meet only one of the two conditions. And a session whose register never came back gets the word "not eligible" written beside it, which reads like a decision and is actually an absence of one.

None of those produce an error. All three produce a column of confident-looking words. The defence is two things this lesson insists on: write the rule so the missing case is a case, and test the rule at its edges rather than in its middle.

Start from the sentence and take it apart.

A session qualifies for the equipment stipend if at least the minimum number of people attended and it ran at a partner site.

"At least" means >=. Not >. This is the single most common error in translating a rule, and it is invisible unless a row happens to sit exactly on the boundary. If one does not, add one to a test block, because next month's data will.

"And" means both, and AND is how you say it. AND(a, b) is TRUE only when both are. OR(a, b) is TRUE when either is. Read the policy sentence aloud: if replacing "and" with "or" changes what it says, the formula must not use OR.

A rule needs a case for "no input". IF has two branches and this rule has three outcomes: qualifies, does not qualify, and cannot be judged. Give the third one its own branch, first, before anything is compared:

=IF(ISBLANK(D2), "attendance not recorded", IF(AND(...), "eligible", "not eligible"))

Order matters here for the same reason it mattered in the last module. A blank cell behaves as zero in a comparison, so a rule that compares first will report a missing register as a session that fell short. That is not a wrong number; it is a claim about a session that nobody measured.

Be equally careful about the opposite mistake. A session that ran with nobody in it has a real, measured attendance of zero. With the current positive minimum of 12, it genuinely does not qualify. If your test for "no input" is D2=0 rather than ISBLANK(D2), you have merged the two cases and lost the distinction the last module spent a lesson building.

Keep the threshold in a cell. The minimum is an assumption. Typed into eight formulas, raising it next term means finding all eight and getting one wrong.

And test the boundary deliberately. A boundary test block is four rows: one below the threshold, one exactly on it, one above it, and one with the input missing. It takes two minutes and it provides evidence about the threshold and missing-input branches. Also test a nonpartner and a recorded zero, because those exercise different parts of the rule. Keep it in the sheet, not in your head, so that whoever changes the rule next can see what it was supposed to do.

Seven sessions. Three of them are the interesting ones:

        C            D          E
      Partner     Attended    Decision
CR-301  yes          12        ?
CR-304  yes       (blank)      ?
CR-307  yes           0        ?

CR-301 sits exactly on the threshold. It qualifies — "at least twelve" includes twelve — and a rule written with > refuses it while looking perfectly reasonable.

CR-304's register never came back. Its attendance is not low; it is unknown. A rule that goes straight to the comparison treats the blank as zero and writes "not eligible", which reads as a finding and is a fabrication.

CR-307 ran and nobody came. Its attendance is zero, measured, real. It does not qualify, and saying so is correct. The sheet has to tell CR-304 and CR-307 apart even though a naive comparison sees the same number in both.

The minimum is 12. Predict the first two formulas for CR-301 (row 2: partner site, 12 attended), then with their references copied to row 5 for CR-304 (partner site, attendance blank). Predict the third formula as written for CR-304, then copied to D8 for CR-307 (partner site, recorded zero).

=IF(AND(D2>12,C2="yes"),"eligible","not eligible")
=IF(AND(D2>=12,C2="yes"),"eligible","not eligible")
=IF(D5=0,"attendance not recorded","judged")

The first attempt:

=IF(AND(D2>12,C2="yes"),"eligible","not eligible")

Three separate corrections, in order of how much damage each one does.

The comparison. > becomes >=, because "at least" includes the boundary.

The threshold. 12 becomes $I$2, so the minimum lives in one cell and raising it is one edit.

The missing case. A third outcome, tested first:

=IF(ISBLANK(D2),"attendance not recorded",
   IF(AND(D2>=$I$2,C2="yes"),"eligible","not eligible"))

Two other spellings do the same job and are worth recognising. ISNUMBER(D2) tested the other way round is equivalent for this fixture's numeric-or-blank attendance. Outside that domain, validate text and errors explicitly rather than treating the tests as interchangeable. And the two conditions can be nested rather than joined:

=IF(ISBLANK(D2),"attendance not recorded",
   IF(C2="yes", IF(D2>=$I$2,"eligible","not eligible"), "not eligible"))

That version is longer, and it has one advantage worth knowing about: it makes it easy to give a different message for each way of failing — "not at a partner site" as against "attendance below the minimum" — which is often what the person reading the column actually wants to know.

A programme lead reports that the stipend list "looks about right" but two sites have complained. One says a session that was full was refused. The other says a session whose register was never returned was listed as ineligible, which it finds odd.

Both complaints are about the same column, and neither is about arithmetic.

1. Find a row on the boundary. Sort by attendance and look at the rows holding exactly the minimum. Check that the site is also a partner. A partner session exactly on the threshold should qualify; if it does not, inspect the comparison and the other inputs. A nonpartner session correctly fails even at the threshold.
2. Count the outcomes. Three counts — eligible, not eligible, unknown — should add up to the number of rows. If the unknown count is absent, add it. Then check known blank-input rows: a missing summary count alone does not tell you how many branches the rule has.
3. Look for rows whose input is missing. Filter for blanks in the attendance column. Those rows should explicitly say "attendance not recorded". An eligibility or refusal label would turn absent evidence into a decision.
4. Check the zero rows separately. With the current minimum of 12, they should say "not eligible". At a minimum of zero, a partner session with recorded zero would qualify. If they say "attendance not recorded", the rule is testing for zero rather than for absent, and it has merged two different situations.
5. Build the boundary block and keep it. Four rows, in the sheet. The complaint that started this could have been answered in ten seconds if one had existed.

The general shape: a rule that produces no errors has told you nothing about whether it is right. Test the threshold, the missing-input branch, recorded zero and both partner states deliberately.

Write the stipend rule, and then prove it at its edges.

Notice which sessions the table has deliberately put in front of you: one exactly on the boundary, one with no attendance recorded, and one that ran with nobody in it. All three have to come out differently.

Find a rule that applies to you — a discount threshold, an expenses policy, an eligibility criterion at work, the small print on a delivery charge — and take it apart on paper.

1. Write the rule as one sentence, exactly as it is published.
2. Underline every comparison word: at least, more than, up to, under, within. For each, write down whether the boundary value is included. Flag any genuine ambiguity rather than assuming there must be one.
3. Write the four boundary tests: one below, one exactly on, one above, one with the input missing.
4. For the missing case, say what should happen. Not what a formula would do — what the organisation would want.

Step 2 is the one that pays. If the wording leaves a boundary unclear, ask the policy owner before encoding an interpretation.

Sign in to track your progress on this exercise.

Move on when you can do these without looking back.

• Say which comparison operator "at least" requires, and what goes wrong with the other one.
• Explain why a rule with two outcomes is the wrong shape for data that has three.
• Give the difference between ISBLANK(D2) and D2=0, and name a situation where confusing them would matter.
• Describe the four rows of a boundary test block and say why the fourth one is the one people leave out.
• Say why the threshold belongs in a cell, and what changes when it does.

Sign in to track your progress on this exercise.

A written rule becomes a correct formula when its comparison includes the boundary the sentence includes, its conjunction is the one the sentence uses, its threshold lives in a cell, and it has a branch for the input that never arrived. Keep the four-row boundary block in the sheet, and also test nonpartner and recorded-zero cases.

Next: Answer grouped questions. The rule now decides one row at a time. The next question is what the rows say together — and the fastest way to get that wrong is to divide by the wrong thing.