1. Build a Crash Injury Severity Scorecard from Raw Incident Data

From Raw Crash Records to a Structured Injury Severity Scorecard

This first case establishes the data foundation for the entire 30-case series. The task is deliberately narrow: take a raw crash incident dataset, import it into Excel in a form that preserves data integrity, and build a basic injury severity scorecard — a lookup structure that converts each injury level (Fatal, Severe, Moderate, Minor, None) into a numeric point value. The scorecard must be structured so that later cases can layer weighted categories and 5-star threshold logic on top of it without rebuilding the data model.

The key design decision here is separation of concerns: raw incident data lives in one table, the scoring rules live in a separate lookup table, and the per-incident score is computed by reference rather than hard-coded. If you embed point values directly into formulas or into the incident table, every later change to the scoring scheme becomes a manual edit across hundreds of rows. A clean scorecard avoids that.

Step 1 — Import the Raw Incident Dataset

Assume the raw dataset arrives as a CSV export with one row per crash incident. A representative structure (example data, not real records) looks like this:

Import via Data → From Text/CSV (Power Query) rather than double-clicking the file. Power Query gives you a repeatable load step: you can set column data types explicitly, and the query can be refreshed when new incident rows arrive. Set the types as follows — this matters because a text-typed numeric column will silently break later arithmetic:

• IncidentID → Text (it is an identifier, not a number)
• Date → Date
• RoadType → Text
• SpeedLimit_kph → Whole Number
• Fatalities, SevereInjuries, ModerateInjuries, MinorInjuries → Whole Number

Load the result to a worksheet as an Excel Table named tblIncidents. Using a structured table (not a plain range) means formulas and later lookups expand automatically as rows are added. Verify the import: the row count should match the source file, and no injury column should contain blanks or text. A quick check is =COUNT(tblIncidents[Fatalities]) — it must equal the table's data-row count.

Step 2 — Build the Injury Severity Scorecard

The scorecard is a small lookup table that maps each injury level to a base point value. Keep it on its own sheet, named Scorecard, as a table called tblScorecard:

These base points are a teaching example, chosen so the ordering is monotonic (Fatal > Severe > Moderate > Minor > None) and the gaps are visible. They are not a validated safety standard. The important structural property is that the scorecard is a single source of truth: every downstream formula reads BasePoints from tblScorecard, so changing a value here propagates everywhere.

Two structural rules make the scorecard safe for later cases:

1. Exact-match keys. The InjuryLevel values must match the column headers used in the incident table exactly (case and spelling). Later SUMPRODUCT/lookup logic depends on this.
2. No merged cells, no blank rows inside the table. A lookup range with a gap will return wrong or #N/A results.

Step 3 — Compute a Per-Incident Base Score

Add a column to tblIncidents named BaseScore. The score for one incident is the sum, across each injury level, of (count × base points). Using the scorecard as the reference, a transparent formula for the first data row is:

= [@Fatalities]      * INDEX(tblScorecard[BasePoints], MATCH("Fatal",    tblScorecard[InjuryLevel], 0))
+ [@SevereInjuries]  * INDEX(tblScorecard[BasePoints], MATCH("Severe",   tblScorecard[InjuryLevel], 0))
+ [@ModerateInjuries]* INDEX(tblScorecard[BasePoints], MATCH("Moderate", tblScorecard[InjuryLevel], 0))
+ [@MinorInjuries]   * INDEX(tblScorecard[BasePoints], MATCH("Minor",    tblScorecard[InjuryLevel], 0))

Enter it once in the BaseScore column of the table and Excel fills the whole column. Because it uses structured references ([@...]) and INDEX/MATCH against tblScorecard, it stays correct when rows are added or point values change. For incident CR-0001 (0 fatal, 1 severe, 2 moderate, 3 minor) the expected result is 0×10 + 1×6 + 2×3 + 3×1 = 15. For CR-0002 (1 fatal, 0 severe, 1 moderate, 0 minor) it is 10 + 0 + 3 + 0 = 13.

How the Pieces Fit Together

The diagram below shows the data model: raw incidents flow in, the scorecard supplies point values by key lookup, and the per-incident base score is the join of the two. This is the structure later cases extend with category weights and star thresholds.

Verification and Common Failure Symptoms

Before moving on, confirm the scorecard is wired correctly with these checks:

• Spot-check two rows by hand. Recompute CR-0001 and CR-0002 manually and compare to the BaseScore column. They must match 15 and 13.
• Change a point value and watch propagation. Temporarily set Severe to 7 in tblScorecard; every incident with a severe injury should increase by its severe count. Undo the change afterward.
• Check for #N/A. A single #N/A in BaseScore almost always means a spelling/case mismatch between an injury column header and an InjuryLevel key, or a trailing space in a key cell.
• Check for text-typed counts. If a score is unexpectedly 0 or a formula errors, confirm the injury columns are Whole Number, not Text.