4. Normalize Injury Counts per 100,000 Population for Star Rating Fairness

Why Raw Injury Counts Mislead

A weighted injury score is an absolute count. A region with 4.2 million residents will almost always accumulate more injuries than a region with 380,000 residents, even if its safety performance is far better. If you feed raw weighted scores directly into a star threshold table, the rating silently measures population size rather than injury risk. Per-capita normalization removes that exposure bias by converting every region's score into a rate per 100,000 population, so the star rating compares like with like.

The normalization formula used throughout this scorecard is:

Normalized Score per 100k = (Weighted Injury Score / Population) * 100000

Two design decisions matter here. First, normalize the weighted score, not the raw incident count — the weights (fatal, severe, minor) are already part of the risk model, and normalizing afterward preserves their relative contribution. Second, keep the population figure in a dedicated column with an explicit unit, because a population entered in thousands instead of persons will inflate every rate by a factor of 1,000 and push every region to the lowest star.

Building the Normalization Layer in Excel

Assume the weighted score engine from the previous step writes its output to column F (header WeightedScore) and that each region row carries its resident count in column D (header Population). Add a normalization block to the right of the score columns:

The IFERROR wrapper is not cosmetic: a blank or zero population produces #DIV/0!, and a single error cell will break any downstream RANK.EQ or lookup that references the range. Returning an empty string keeps the range clean while still flagging the row as unrated. The RANK.EQ with order 1 ranks ascending, so rank 1 is the lowest rate — the safest region — which is the intuitive direction for a safety scorecard.

Recalibrating the Star Thresholds for Rates

The threshold table from the earlier design step was calibrated against raw weighted scores. It cannot be reused unchanged: a raw-score threshold of, say, 1,500 means something completely different when applied to a rate of 44.29. Rebuild the threshold table in rate units, anchored to the actual distribution of RatePer100k across your 30 regions.

A defensible calibration procedure is to compute the five-number summary of the rate column and set cut points at meaningful quantiles rather than round numbers:

1. Place the sorted rate column in a helper range, or use =QUARTILE.INC($G$2:$G$31,1) and =QUARTILE.INC($G$2:$G$31,3) for the 25th and 75th percentiles.
2. Set the 5-star ceiling at the 20th percentile, 4-star at the 40th, 3-star at the 60th, 2-star at the 80th, and 1-star above that.
3. Store the four cut points in a small lookup block and drive column I with =IFERROR(LOOKUP(G2,$K$3:$K$6,$L$3:$L$6),""), where K holds ascending cut points and L holds the corresponding star values.

Quantile-based cut points guarantee a roughly balanced star distribution by construction, which is exactly the property the threshold-design step was trying to achieve — but now it holds in rate space, so a small dense region and a large sparse region with identical per-capita risk receive identical stars.

How Normalization Flows Through the Rating Logic

The diagram below traces a single region's data from raw weighted score through population exposure to the normalized rate and the final star lookup, and contrasts it with the biased path that skips normalization.

Verifying the Normalization Is Working

Run three checks before trusting the star column. First, confirm the arithmetic on one row by hand: 1,860 / 4,200,000 × 100,000 = 44.29, matching G2. Second, confirm the direction of the ranking: sort by RatePer100k ascending and verify that rank 1 has the lowest rate and the highest star count. Third, confirm the distribution is balanced using the COUNTIF check above.

Typical failure symptoms and their causes:

• Every region shows 1 star. Population was entered in thousands (e.g., 4200 instead of 4200000), inflating every rate by 1,000×. Fix the population column, not the thresholds.
• Blank star cells. A zero or missing population triggered IFERROR to return an empty string. Locate the row with a blank G value and correct the population entry.
• Stars unchanged after editing population. The threshold lookup range was entered as absolute references to the wrong block, or calculation is set to manual. Press F9 to recalculate and re-check the LOOKUP range.
• One region dominates the top band. A single extreme rate is stretching the quantiles. Inspect the sorted rate column for an outlier before adjusting cut points — that is a data-quality issue, not a calibration issue.