5x5 risk matrix formula: score Likelihood x Impact and assign severity

Turn qualitative risk registers into numeric 1–25 heat scores

This illustrative risk register assigns five likelihood labels and five impact labels an ordinal score from 1 to 5.

XMATCH returns each label's 1-to-5 ordinal position, multiplying them produces an illustrative 1–25 product score (Likely 4 * Major 4 = 16), and IFS classifies the score into action bands (>=15 Critical, >=8 High, >=4 Medium, else Low).

1–25 risk score and severity band formulas

Bind s = LikelihoodIdx * ImpactIdx inside LET so you can test ISNA for misspelled labels.

=LET(l, XMATCH(A2, {"Rare","Unlikely","Possible","Likely","Almost Certain"}), i, XMATCH(B2, {"Negligible","Minor","Moderate","Major","Catastrophic"}), IF(OR(ISNA(l), ISNA(i)), "Invalid rating", IFS(l*i>=15, "Critical", l*i>=8, "High", l*i>=4, "Medium", TRUE, "Low")))

Raw numeric risk score (1 to 25)

Output the numeric score in an adjacent column to sort risks highest-first.

=XMATCH(A2, {"Rare","Unlikely","Possible","Likely","Almost Certain"}) * XMATCH(B2, {"Negligible","Minor","Moderate","Major","Catastrophic"})

Inputs and interpretation

The band thresholds and labels are example choices, not an ISO-27001 requirement or a calibrated probability/loss model. Ordinal ratings are not ratio measurements; equal products can represent very different risks. Define criteria and response policies with the responsible team before operational use. Validate both labels; the raw-score formula has no invalid-label fallback.

Further reading

• Microsoft Support — IFS function