AIAG-VDA FMEA: S, O, D and Action Priority, wired up in Excel

What actually changed from the 4th edition, and how to make the rating step auditable in a spreadsheet.

The 2019 AIAG-VDA handbook did not just renumber the rating tables. It replaced the single risk number with a three-way lookup, which is exactly the part most spreadsheets get wrong.

On the tables themselves. The severity, occurrence, detection and Action Priority tables are published in the AIAG-VDA FMEA Handbook and are copyrighted. They are not reproduced here. This page is about the mechanics — the shape of the rating step and how to implement it — so you still need the handbook (or your customer's rating set) for the wording of each level.

What changed

	AIAG 4th edition	AIAG-VDA (2019)
Risk output	RPN = S × O × D	Action Priority (AP): High / Medium / Low
How it is derived	Multiplication	Lookup against a defined S/O/D combination table
Process	Form-driven	Seven steps, from planning through results documentation
Rating tables	One general set	Separate, more concrete sets for Design and Process FMEA

Why the change matters arithmetically

Multiplication hides things. Under RPN, a severity 10 with low occurrence and low detection (10 × 2 × 2 = 40) scores lower than an annoyance failure that happens often and is hard to catch (4 × 6 × 6 = 144). The first one can hurt somebody. AP exists because a product of three ordinal scales is not a meaningful number — the ranks are not on a ratio scale, so multiplying them is arithmetic without meaning.

AP fixes this by making severity dominant: high severity does not get averaged away by good detection. If you are still reporting RPN internally, this is the single strongest argument for moving.

The three ratings, in shape

Rating	Scale	Answers	Where the evidence comes from
Severity (S)	1–10	How bad is the effect, if it happens?	Effect on the end user, the receiving plant and your own plant — rated separately in PFMEA
Occurrence (O)	1–10	How likely is this cause?	Process controls in place, history of the same process, not a guess about the part
Detection (D)	1–10	How likely are we to catch it before it ships?	The detection control's ability, not the inspector's diligence

Two traps here are worth naming because they survive every revision of the standard:

Severity is a property of the effect, not of the failure. It does not change because you added an inspection. If severity moves, the design changed.
Occurrence is about the cause, not the failure mode. One failure mode with four causes gets four occurrence ratings, not one.

Building the AP lookup in Excel

AP is a function of the triple (S, O, D). The clean way to hold it in a worksheet is a flat lookup table with a composite key, so that every assignment is visible and auditable rather than buried in nested IFs.

1. Lay out the lookup sheet

One row per valid combination, with a key built from the three ratings:

Sheet "AP_Table"
A          B    C    D    E
Key        S    O    D    AP
=B2&"-"&C2&"-"&D2   ...  (H / M / L from the handbook)

Padding keeps 10 from sorting next to 1 if you ever sort the sheet:

A2:  =TEXT(B2,"00")&"-"&TEXT(C2,"00")&"-"&TEXT(D2,"00")

2. Look it up from the FMEA row

With severity in M5, occurrence in N5 and detection in O5:

Modern Excel:
=IFERROR(XLOOKUP(TEXT(M5,"00")&"-"&TEXT(N5,"00")&"-"&TEXT(O5,"00"),
                 AP_Table!$A:$A, AP_Table!$E:$E), "")

Older Excel:
=IFERROR(INDEX(AP_Table!$E:$E,
               MATCH(TEXT(M5,"00")&"-"&TEXT(N5,"00")&"-"&TEXT(O5,"00"),
                     AP_Table!$A:$A, 0)), "")

Wrap it in IFERROR returning "", not "L". A missing combination should look empty and wrong, not quietly become the lowest priority.

3. Make blanks behave

A half-filled row should not produce an AP at all:

=IF(COUNT(M5:O5)<3, "", <the lookup above>)

4. Colour it with conditional formatting, not a helper column

Three rules on the AP cell, formula-based, applied to the row range:

=$P5="H"    →  red fill
=$P5="M"    →  amber fill
=$P5="L"    →  green fill

Lock the column with $ and leave the row relative so the rule can be applied down the whole table in one go.

What to sanity-check before you trust the sheet

Count your lookup rows. 10 × 10 × 10 = 1,000 possible triples. If your table has fewer, know which ones are intentionally absent.
Feed it a severity 10 row with O = 1 and D = 1. Confirm the result is not "Low" by construction — that is the exact case RPN handled badly.
Change one rating and watch the AP cell recalculate. A pasted-in value that never moves is the most common defect in inherited FMEA workbooks.
Check that severity is not driven by a formula. It should be an input.

Template

Our FMEA Excel template ships with severity / occurrence / detection inputs and automatic risk scoring and colour banding, as plain .xlsx worksheet formulas — no macros or add-ins, so you can see and change every formula above. If you are also building the capability side of the file, the Cpk vs Ppk formulas are laid out the same way.