3. Validate Injury Score Inputs with Data Validation Rules

Why Validation Belongs at the Input Layer

The star rating in this workbook is only as trustworthy as the severity values that feed it. A composite injury score is a weighted sum of severity counts, and the rating formula maps that sum onto a 1–5 threshold table. If a single cell in the severity column contains a typo such as Moderatee, a stray number, or a trailing space, the weighted lookup either returns #N/A or silently contributes zero — and the star rating for that incident becomes wrong without any visible error. Data validation is the cheapest defense: it stops the bad value at the moment of entry, before it can propagate into the score and the rating.

This case builds a controlled severity input column backed by a named list, adds a numeric guard for the weighted count fields, and verifies that the rating formula can no longer be corrupted by free-text entry. The workbook from the earlier scorecard case is the starting point; the changes here are confined to the input columns and a small validation-support area.

Workbook Layout and the Validation Source List

Assume the scorecard sheet is named Incidents with headers in row 1. The relevant columns are:

Create a dedicated sheet named Lists. In Lists!A2:A6 enter the five severity labels exactly as the scorecard expects them, in ascending order of weight:

Lists!A1  Severity
Lists!A2  Minor
Lists!A3  Moderate
Lists!A4  Serious
Lists!A5  Severe
Lists!A6  Fatal

Select Lists!A2:A6 and define a workbook name SeverityList (Formulas → Define Name, or the Name Box). Using a named range rather than a hard-coded range means the validation rule survives row insertions on the Lists sheet and can be reused by the weighted lookup in the score formula.

Applying the List Validation to the Severity Column

Select Incidents!C2:C1000 (leave room for future rows), then open Data → Data Validation. Configure:

• Allow: List
• Source: =SeverityList
• In-cell dropdown: checked
• Ignore blank: unchecked — a blank severity must not silently pass as valid
• Error Alert: Stop, title Invalid severity, message Choose Minor, Moderate, Serious, Severe, or Fatal from the list.
• Input Message: optional, e.g. Select the highest injury severity recorded for this incident.

The Stop style is deliberate. Warning and Information styles still allow the user to commit the invalid value, which defeats the purpose. Stop rejects the entry and keeps the previous cell content.

Guarding the Numeric Count Column

The weighted score multiplies InjuryCount by the severity weight, so a negative or fractional count produces a nonsensical score. Apply a second rule to Incidents!D2:D1000:

• Allow: Whole number
• Data: greater than or equal to 0
• Error Alert: Stop, message Injury count must be a whole number of 0 or more.

If the scorecard also records a separate fatality count, repeat the same whole-number rule on that column. Keep the upper bound open unless your data model has a defensible maximum; an arbitrary cap such as 50 will eventually reject a legitimate mass-casualty record.

How the Validation Protects the Rating Chain

The diagram below shows the dependency chain from the controlled input cells through the weighted score to the star rating, and marks where each validation rule intercepts bad data.

Verifying the Rules Actually Block Bad Data

Validation is only proven by attempting the failure. Run these checks on a copy of the workbook:

1. In C2, type Moderatee and press Enter. Expected: the Stop alert appears and the cell retains its prior value. If the value is accepted, the rule is attached to the wrong range or the error style is not Stop.
2. Type moderate (lowercase). Expected: rejected, because list matching is case-insensitive for equality but the stored value must match a list entry — confirm the cell does not end up holding a differently-cased string that later fails the lookup. If your lookup uses exact MATCH, keep the list and entries consistently cased.
3. Paste a block of mixed values into C2:C10. Expected: Excel rejects the paste or flags the invalid cells depending on version; verify no invalid string survives in the column.
4. In D2, enter -3 and then 2.5. Expected: both rejected by the whole-number rule.
5. Clear C2 entirely. Expected: rejected because Ignore blank is off, forcing an explicit severity choice.

After the checks, confirm the downstream formulas still evaluate: a valid row should produce a numeric CompositeScore and an integer StarRating between 1 and 5. A row that previously showed a wrong rating due to a typo should now either be corrected or blocked from entry.

Reusable Validation Checklist

With these rules in place, the severity column becomes a controlled vocabulary and the count column a bounded numeric field. The star rating formula now receives only values it was designed to handle, so any remaining rating anomaly points to the threshold table or the formula itself rather than to corrupted input.