Level 8 · Boss: replay the order-entry log through the new rules

The log and the rules

The sheet holds 36 order-entry rows from support’s correction sheet, as they were typed: entry id, order, channel, product, quantity and unit price. IT’s new form will enforce two rules, and Jennifer wants to know what they would have done to this batch before switching them on.

The inputs sit on the right: the allowed channels in K2:K4 and the “big line” threshold in L6. Nothing in the data has been fixed. You’re finding the problems, not hiding them.

Step 1: one column per rule

In Excel, the quantity rule is Data → Data Validation → Allow: Whole number, Data: greater than, Minimum: 0. Written as a Custom rule that you can also run over old rows:

G2   =AND(ISNUMBER(E2),E2=INT(E2),E2>0)

Each part catches a different entry. ISNUMBER fails the "2" in E10, pasted in as text. E2=INT(E2) fails the 1.5 in E21: INT(1.5) is 1. E2>0 fails the 0 in E35 and the -4 in E26. Leave out any one of them and that row slips through.

The channel rule is Allow: List, Source: =$K$2:$K$4, which gives the cell a drop-down. Its formula form counts the entry in the list:

H2   =COUNTIF($K$2:$K$4,C2)>0

phone in C18 and website in C26 aren’t on the list. C29 looks like web but is "web " with a trailing space, so it doesn’t match either. In the real form, the drop-down stops all three. Note that both the List rule and COUNTIF ignore case: Web would pass. If case matters, use SUMPRODUCT(--EXACT($K$2:$K$4,C2))>0.

Step 2: a reason per row

I2   =IFS(NOT(G2),"bad quantity",NOT(H2),"bad channel",TRUE,"ok")
L8   =COUNTIF(I2:I37,"<>ok")

IFS returns the first reason that applies, like a form that stops at the first field that fails. Row 26 fails both rules and reports bad quantity. L8 counts 6 rejected entries: rows 10, 21, 26 and 35 for quantity, 18 and 29 for channel.

This is also where the error messages come from. For each rule, write what the person typing should do, and put it in the rule’s Error Alert tab (Style: Stop): “Quantity must be a whole number above 0” and “Pick a channel from the list”. An Input Message (“Whole units, e.g. 2”) prevents most of these before they happen.

Step 3: the highlight rule

Lines over $500 that pass get a second look. In Excel, select A2:F37, then Conditional Formatting → New Rule → Use a formula:

J2   =AND($I2="ok",$E2*$F2>$L$6)

The columns are locked so every cell in a row reads that row’s status, quantity and price; the rows are free so each row reads its own. Three lines light up: row 4 (4 × 149.00 = 596.00), row 15 (2 × 349.00 = 698.00) and row 34 (999.00). Row 10 would be a fourth without the status test: the text "2" times 349.00 is 698.00, because arithmetic converts numeric text.

Thinking like an analyst

Before switching validation on, a replay like this answers three questions:

• Are the rules right? If half the batch failed on a legitimate value (say, a quantity of 0 for a pure price correction), the rule needs changing, not the people.
• What’s already broken? Validation only checks new entries; the six rows here stay as they are unless someone fixes them. Circle Invalid Data shows them in Excel.
• What gets around it? Paste replaces validation, so a sheet that’s pasted into needs its rules backed by a check column like these, or a protected sheet.

Common mistakes

• E2>0 alone as the quantity rule: "2" and 1.5 both pass.
• An unlocked list: COUNTIF(K2:K4,C2) slides down to K3:K5 and starts rejecting web.
• Testing the channel first in IFS, which changes the reason on row 26.
• A highlight rule without $ before the columns: in Excel each cell of the row would test a different column, and the row lights up in patches.