2. Design a 5-Star Rating Formula for Crash Injury Scores

Why a Parameter Table Beats Hard-Coded Thresholds

In the previous scorecard, each incident already carries a composite injury score — a weighted sum of severity counts (for example, Fatal×10 + Severe×5 + Moderate×2 + Minor×1). The remaining task is to translate that continuous score into a discrete 1–5 star rating. The temptation is to bury the cut-offs inside a single long formula. That works until a safety committee decides a "Severe" injury should weigh more, or a regulator redefines what counts as a 4-star event. Then every formula in the sheet must be edited by hand, and the risk of an inconsistent edit is high.

The robust pattern separates policy from mechanism. The policy — the score boundaries that define each star band — lives in a small, clearly labelled parameter table. The mechanism — the formula that reads those boundaries and returns a rating — lives in the data table and references the parameter table by absolute cell address. Change a boundary once, and every rating recalculates.

Building the Parameter Table

Place the parameter table on a dedicated area of the sheet (or a separate Parameters sheet). It needs one row per star band, with a lower bound for the band. Because the bands are contiguous and ordered, you only need the minimum score that qualifies for each star level; the formula walks the table and picks the highest band whose minimum is not exceeded.

Assume this table occupies Parameters!A2:B6, with stars in column A and minimum scores in column B, sorted ascending by MinScore. The ascending sort is a hard requirement for the lookup approach below — an unsorted table silently returns wrong bands.

Approach A — Nested IF

The nested IF is the most transparent option and the easiest to audit line by line. It tests the composite score against each boundary from the top down and returns the first band it satisfies. With the composite score in D2:

=IF(D2>=Parameters!$B$6,5,
  IF(D2>=Parameters!$B$5,4,
   IF(D2>=Parameters!$B$4,3,
    IF(D2>=Parameters!$B$3,2,1))))

Read it as: "if the score reaches the 5-star floor, return 5; otherwise if it reaches the 4-star floor, return 4; … otherwise it is a 1." Because the tests run from the highest boundary downward, the first true branch wins and lower branches are never reached — this is what makes the ordering correct. Every boundary is a cell reference, so editing Parameters!B5 from 16 to 18 immediately reclassifies scores in the 16–17 range from 4 stars down to 3.

Approach B — Lookup-Based

When the number of bands grows or the policy is likely to change often, a lookup is cleaner than a deep nest. The classic technique is an approximate-match VLOOKUP (or LOOKUP) against the sorted MinScore column:

=VLOOKUP(D2,Parameters!$B$2:$A$6,2,TRUE)

Two details make this work. First, the fourth argument is TRUE (approximate match), which finds the largest MinScore that is less than or equal to the score. Second, the lookup range is written B2:A6 — the score column first, the star column second — because VLOOKUP searches the leftmost column of its range and returns from a column to its right. Writing the range in this reversed order lets the score drive the search while the star is returned. The table must be sorted ascending on MinScore; if it is not, approximate match returns an unreliable band.

An equivalent and slightly more readable form uses LOOKUP, which is inherently approximate and needs no column index:

=LOOKUP(D2,Parameters!$B$2:$B$6,Parameters!$A$2:$A$6)

Both lookup forms and the nested IF must agree. If they disagree on a given score, the usual cause is an unsorted parameter table or a boundary typed as text instead of a number.

How the Two Mechanisms Relate to the Boundaries

The diagram below shows the score axis with the five bands, the boundary values pulled from the parameter table, and how each mechanism resolves a score to a star. It also marks the two classic failure points: an unsorted table and a text-typed boundary.

Verifying the Formula Against the Boundaries

Do not trust a rating formula until it has been tested at the edges. Build a small verification block that feeds known scores through the formula and checks the returned star. The critical cases are the boundary values themselves and the values one unit below them, because off-by-one errors hide exactly there.

If any row disagrees with the expected star, the fault is almost always one of three things: the parameter table is not sorted ascending on MinScore; a boundary cell contains text (for example a trailing space or a value entered as "16 ") so the numeric comparison silently fails; or the nested IF tests were written bottom-up instead of top-down, letting a lower band capture a score that belongs to a higher one. Fixing the parameter table fixes all three at once, which is the whole point of keeping thresholds out of the formula.

Making the Rating Robust to Blank and Out-of-Range Scores

Real scorecards contain empty rows and, occasionally, a score that exceeds the top boundary. Wrap the mechanism so it degrades gracefully rather than showing a misleading star. A defensive version of the nested IF checks for a blank first and clamps the top band:

=IF(D2="","",
  IF(D2>=Parameters!$B$6,5,
   IF(D2>=Parameters!$B$5,4,
    IF(D2>=Parameters!$B$4,3,
     IF(D2>=Parameters!$B$3,2,1)))))

Because the top test is >= B6, any score at or above 25 — including 40 or 100 — correctly returns 5 rather than falling through. The blank guard returns an empty string so unfilled rows do not display a spurious 1-star rating. The lookup form needs the same guard: =IF(D2="","",LOOKUP(D2,Parameters!$B$2:$B$6,Parameters!$A$2:$A$6)).

Deliverable: Parameter Table and Formula Pair

The reusable output of this case is the parameter table plus the two interchangeable formula forms. Fill the table with your own policy boundaries, keep it sorted ascending, and paste the formula into the rating column of the scorecard. The nested IF is preferred when auditors need to read the logic directly; the lookup is preferred when the band count is large or changes frequently. Either way, the boundaries live in one place, and a single edit propagates to every rating in the workbook.