3. Mapping NCAP protocols to spreadsheet formulas

An NCAP rating workbook is only as trustworthy as the translation layer between the published protocol and the cells that compute the score. That translation layer has three distinct jobs: encode the scoring rules (how a measured value becomes points), encode the weightings (how points from different tests combine into a domain score), and encode the penalty conditions (how non-compliance, capping, or disqualifying events override the arithmetic). Most automation failures in rating calculators do not come from bad Python — they come from a formula layer that silently conflates these three jobs, so a penalty ends up averaged instead of applied, or a weighting is hard-coded in a cell that nobody can audit.

The practical discipline is to separate inputs, rule parameters, and computation into different regions of the workbook, and to give every rule parameter a named range. When a protocol revision changes a threshold from 0.35 to 0.40, you should be editing one named cell, not hunting through forty formulas.

Structuring the workbook into three layers

A workable layout for a frontal-impact style domain looks like this. The Inputs sheet holds raw measured values per test (dummy readings, sled decelerations, measured forces) with one row per test and a stable test ID. The Params sheet holds every protocol constant — thresholds, slope coefficients, caps, weightings — each as a single cell with a defined name. The Calc sheet references only named ranges and input cells; it contains no literal numbers other than 0 and 1. This is the rule that makes the workbook auditable: if a formula on Calc contains 0.35, that constant belongs on Params instead.

Named ranges are the contract between the protocol document and the spreadsheet. Define them with a consistent prefix so they sort together and read clearly in the Name Manager: P_ for parameters, W_ for weightings, CAP_ for caps, IN_ for inputs. A parameter such as the chest deflection limit becomes P_chest_defl_limit; the domain weighting for the frontal test becomes W_frontal. Because Excel named ranges are workbook-scoped by default, a Python script writing into the workbook can address the same names through openpyxl's defined_names, which keeps the automation and the manual audit path aligned.

Translating scoring rules into functions

NCAP scoring rules are usually piecewise: full points below a threshold, a linear ramp between two thresholds, zero above an upper limit. The cleanest Excel expression is a nested IF or, better, a clamped linear interpolation. For a criterion where a measured value x earns full points at or below P_x_full and zero at or above P_x_zero, with maximum points P_x_max:

=P_x_max * MEDIAN(0, (P_x_zero - x) / (P_x_zero - P_x_full), 1)

MEDIAN here acts as a clamp to the interval [0, 1], which is more compact and less error-prone than nested IFs and behaves correctly when x falls outside the ramp. Note the direction convention: this formula assumes lower is better, so the numerator is P_x_zero - x. For criteria where higher is better (for example, a minimum headroom), invert the numerator to x - P_x_zero and swap the thresholds accordingly. Fix this convention once, document it on the Params sheet, and apply it everywhere — mixed conventions are the most common source of sign errors in rating workbooks.

Where the protocol defines a stepped score rather than a ramp, use a lookup against a small table on Params with XLOOKUP or INDEX/MATCH in approximate-match mode, and keep the step boundaries as named ranges so the table itself is the single source of truth.

Combining criteria into a domain score with weightings

Once each criterion yields points, the domain score is a weighted sum normalized by the total weight. Put the per-criterion points in a contiguous block and the matching weights in a parallel block on Params, then use SUMPRODUCT:

=SUMPRODUCT(Calc!D5:D12, Params!W_criteria) / SUM(Params!W_criteria)

Keeping the weights in a named block rather than as individual scalars makes the normalization automatic and prevents the classic error of dividing by a hard-coded total that drifts when a criterion is added. If the protocol specifies that weights must sum to 1.0, add a validation cell on Params — =ABS(SUM(W_criteria)-1)<1E-9 — and surface it as a visible flag rather than letting a mis-summed weight set quietly rescale every score.

Encoding penalty and capping conditions

Penalties are not weights and must not pass through the weighted sum. Model them as a separate, ordered stage applied after the domain score is computed. Typical NCAP penalty logic includes: a cap on the domain score when a specific criterion falls below a minimum, a fixed deduction when a structural or restraint condition is violated, and a disqualification that forces the domain to zero. Implement these as an explicit precedence chain so the order is visible and testable:

=IF(IN_disqualify=TRUE, 0,
   IF(IN_cap_trigger=TRUE, MIN(domain_score, CAP_domain_max),
      MAX(0, domain_score - P_penalty_fixed)))

The precedence matters: a disqualification must dominate a cap, and a cap must dominate a deduction. Writing the chain top-down in this order makes the intent readable and means a reviewer can verify the logic without reconstructing it from scattered cells. Keep each trigger (IN_disqualify, IN_cap_trigger) as a boolean input cell driven either by manual entry or by a Python pre-check, never as an inline condition buried inside the score formula.

Worked example: one criterion end to end

Consider a chest-deflection criterion with P_x_max = 4, full points at or below P_x_full = 22 mm, zero points at or above P_x_zero = 50 mm, and a measured input of x = 31 mm. The ramp fraction is (50 − 31) / (50 − 22) = 19 / 28 ≈ 0.679, so the criterion earns 4 × 0.679 ≈ 2.71 points. If the domain weighting for this criterion is 0.25 and the other criteria contribute a weighted subtotal that brings the normalized domain score to 3.42, and no cap or disqualification trigger is set, the final domain score is MAX(0, 3.42 − 0) = 3.42. Change the input to x = 55 mm and the ramp clamps to zero, the criterion earns 0, and the domain score drops accordingly — the same formula handles both cases without a separate branch.

To verify the mapping, build a small test block on a Tests sheet with boundary inputs: exactly at P_x_full, exactly at P_x_zero, one unit inside each, and one value far outside on each side. The expected outputs are full points, zero points, a value strictly between, and the clamped extremes. If any boundary case disagrees, the fault is almost always a swapped threshold or an inverted numerator — check the direction convention first, then the named-range references.

The diagram makes the separation explicit: parameters and inputs are read-only sources, scoring and weighting are arithmetic stages, and the penalty chain is an ordered override applied last. When a protocol revision arrives, the change should land in the Params block or in the penalty precedence — never in the middle of a scoring formula. That is the test of a well-mapped workbook: the diff between two protocol versions is small, localized, and readable.

A final audit habit: open the workbook's Name Manager and confirm every name used on Calc resolves to a single cell or a contiguous block on Params or Inputs. Any name pointing at a formula, or any formula containing a bare numeric literal other than 0 or 1, is a mapping defect waiting to surface during a rating review.