2. Design a 5-Star Rating Threshold Table with Weighted Injury Categories

Why a Threshold Table Needs Weights First

A 5-star rating is only as trustworthy as the score it consumes. Before any star boundary can be drawn, the scorecard must express that a fatality is not "one more injury" than a minor one. The threshold table therefore sits downstream of a weighted injury category model: each injury category carries a multiplier, and the total weighted score per incident is the sum of category counts times their weights. The threshold table then maps that continuous score onto five discrete bands.

For this case we use a compact, auditable weight set. Weights are chosen so that a single fatal injury dominates the scale, severe injuries cluster in the upper bands, and minor injuries alone can never reach the top stars. The weights are deliberately round numbers so that reviewers can sanity-check any incident by hand.

With these weights, a weighted score is computed per incident as Score = 10·F + 4·S + 1·M. The threshold table's job is to convert that score into a star rating and to prove the resulting distribution is balanced rather than collapsed into one or two bands.

Designing the Threshold Bands

Thresholds should be derived from the score distribution, not guessed. The practical method is to compute the weighted score for every incident, sort the scores, and place cut points at target percentile boundaries. For a balanced 5-star scheme, a common target is roughly 10% / 20% / 40% / 20% / 10% across stars 1 through 5, but the exact split is a policy decision. What matters is that the cut points are explicit, monotonic, and validated against the actual data.

Assume a teaching dataset of 40 incidents with the following weighted-score summary (illustrative example values, not measured results):

Note the direction: lower score = safer = more stars. This is a deliberate convention and must be stated once and held everywhere — in the table, in formulas, and in any chart. Reversing it silently is the most common source of misread dashboards.

Building the Table in Excel

Lay out the threshold table with the lower bound of each band in ascending order, because LOOKUP and VLOOKUP(..., TRUE) require sorted ascending keys. Store the minimum score that qualifies for each star, then use an approximate match.

Thresholds sheet:
A1: MinScore   B1: Stars   C1: Label
A2: 0          B2: 5       C2: 5-Star (Safest)
A3: 1          B3: 4       C3: 4-Star
A4: 2          B4: 3       C4: 3-Star
A5: 5          B5: 2       C5: 2-Star
A6: 10         B6: 1       C6: 1-Star (Most Severe)

Incident sheet (row 2 example):
D2: Fatal count   E2: Severe count   F2: Minor count
G2 (weighted score): =10*D2 + 4*E2 + 1*F2
H2 (stars):          =LOOKUP(G2, Thresholds!$A$2:$A$6, Thresholds!$B$2:$B$6)

The LOOKUP form is intentional: it finds the largest MinScore that does not exceed the incident score, which is exactly the band definition above. Because the keys are ascending and the bands are contiguous, no gaps or overlaps are possible. If you prefer VLOOKUP, use =VLOOKUP(G2, Thresholds!$A$2:$B$6, 2, TRUE) with identical results.

Validating a Balanced Distribution

A threshold table is only acceptable once you have counted how many incidents land in each star. Build a small validation block using COUNTIFS against the star column, then compare actual share to target share.

Validation block:
J1: Stars  K1: Count  L1: Share  M1: Target  N1: Delta
J2: 5      K2: =COUNTIF($H$2:$H$41, J2)
           L2: =K2/COUNTA($H$2:$H$41)
           M2: 0.10
           N2: =L2 - M2
(repeat for stars 4,3,2,1 with targets 0.20, 0.40, 0.20, 0.10)

Read the Delta column as the tuning signal. A large positive delta on 1-star means the top band is too wide (threshold too low); a large negative delta means it is too narrow. Adjust the MinScore cut points by one or two points and re-read the deltas. Two or three iterations usually converge. A practical acceptance rule: every star band is non-empty, and no band's share deviates from target by more than about 5 percentage points.

How the Pieces Fit Together

The diagram below shows the data path from raw category counts through weighting, scoring, and threshold lookup to the validated star distribution. It is the mental model to keep while editing the workbook: weights feed the score, the score feeds the lookup, and the lookup output is what the validation block counts.

Common Failure Symptoms and Fixes

• All incidents land in one star. The score range is too narrow relative to the cut points, or weights are too small. Check the min/max of the score column; if the spread is under 5, the bands cannot separate.
• #N/A from LOOKUP. The threshold keys are not sorted ascending, or the incident score is below the smallest key. Ensure MinScore starts at 0.
• Stars inverted. The lookup returns 5 for the highest score. Confirm the band direction: lower score must map to more stars.
• Distribution drifts after data refresh. Thresholds were tuned to an old dataset. Re-run the validation block and re-tune cut points against the current population.

Once this table is stable, the star rating becomes a deterministic function of the weighted score, and any downstream comparison — by region, by year, or by vehicle type — inherits a rating whose boundaries are explicit and whose balance has been demonstrated rather than assumed.