Excel guide · 5 min read
How to calculate first pass yield (FPY) in Excel
=C2/B2, units passed first time over units entering the step. Rolled throughput yield, the chance a unit gets through every step without rework, is the product of the step yields: =PRODUCT(G2:G6). In the sample, final yield is 95.4% but rolled throughput yield is only 79.3%.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Step | Units in | Passed first time | Passed after rework | Scrapped | Good out | First pass yield | Defects found at final test | 41 | |
| 2 | Cutting | 1000 | 970 | 18 | 12 | 988 | 0.97 | Opportunities per unit | 12 | |
| 3 | Welding | 988 | 921 | 52 | 15 | 973 | 0.93218623482 | |||
| 4 | Painting | 973 | 905 | 61 | 7 | 966 | 0.93011305242 | |||
| 5 | Assembly | 966 | 940 | 20 | 6 | 960 | 0.97308488613 | |||
| 6 | Final test | 960 | 930 | 24 | 6 | 954 | 0.96875 |
Download this sheet as an Excel practice file (.xlsx)
Lay out one row per process step
For each step, record units in (B), units that passed first time (C), units that passed after rework (D) and units scrapped (E). Good out in F2 is =C2+D2. Units in for the next step is the good output of the one before, so B3 is =F2, filled down. Only the first step's input, 1,000 in B2, is typed.
Add a check: =SUMPRODUCT(--(B2:B6-C2:C6-D2:D6-E2:E6<>0)) counts rows where in doesn't equal first-pass + rework + scrap. It should be 0. When it isn't, some units were counted twice or are still sitting at a station.
Result: 0
0: every step's units balance.
First pass yield per step
G2 is =C2/B2, formatted as a percentage. Cutting passes 97% first time; Welding 93.2%; Painting 93.0%; Assembly 97.3%; Final test 96.9%. Yield counts units that failed; to check whether a measured dimension can stay inside its tolerance at all, calculate process capability (Cp and Cpk).
To name the weakest step without scanning: =XLOOKUP(MIN(G2:G6),G2:G6,A2:A6) returns Painting. That's the step where 61 units needed a second go, and the one to look at first.
Result: 0.93011305242 shows as 93.0% with Percent format
0.9301...: Painting passes 93.0% of units first time (905 of 973).
Rolled throughput yield: multiply, don't average
Rolled throughput yield (RTY) is the probability that one unit gets through the whole process without any rework or scrap. Because each step is a separate chance to fail, the yields multiply: =PRODUCT(G2:G6).
Averaging the five yields gives 95.5%, which sounds great and means nothing. Multiplying gives 79.3%: out of 1,000 units started, only about 793 go through every step clean. The more steps a process has, the further RTY falls below any single step's yield.
Result: 0.79281635662 shows as 79.3% with Percent format
0.7928...: rolled throughput yield of 79.3%.
Which step to fix first: RTY if one step were perfect
The lowest FPY isn't automatically the best step to work on, because the gain depends on how far each step is from 100%. Divide RTY by each step's yield and you get the RTY you'd have if that step never failed. One formula spills the answer for all five steps.
Painting (85.2%) and Welding (85.0%) are almost tied: fixing either lifts RTY by about six points, while a perfect Cutting, Assembly or Final test step adds only about two. So the shortlist is two steps, not one, and the tie-breaker is cost: a weld repair and a repaint rarely take the same time or material, so compare the rework cost per unit at each step before you choose. =XLOOKUP(MAX(PRODUCT(G2:G6)/G2:G6),PRODUCT(G2:G6)/G2:G6,A2:A6) names the top step if you want it in one cell.
Result: spills 5 rows × 1 column
| 0.81733645012 |
| 0.8504913793 |
| 0.85238708838 |
| 0.81474531967 |
| 0.8183910778 |
Spills 0.8173, 0.8505, 0.8524, 0.8147 and 0.8184: RTY with Cutting, Welding, Painting, Assembly or Final test made perfect.
What if Painting only improves to 97%, not 100%?
Swap its yield for the target inside PRODUCT and leave the other steps as they are.
Result: 0.82681547573
0.8268: RTY rises from 79.3% to 82.7%, about 34 more clean units per 1,000 started.
Count a failure at the step that found it, and say so
Final test often finds defects that were made earlier, such as a weld that cracks under load. If you book that unit as a Final test failure, Welding looks better than it is. Pick one rule and write it above the table: either count failures where they were found (simple, and what most inspection logs record) or add a Cause step column and count them where they were made.
If your data is an inspection log with one row per unit per step and a Result of Pass, Rework or Scrap, you don't need the summary table typed by hand. Per step, FPY is =COUNTIFS(Log[Step],A2,Log[Result],"Pass")/COUNTIFS(Log[Step],A2): first-time passes over everything inspected at that step. A unit that's reworked and re-inspected must not get a second Pass row, or it will be counted as a first-time pass.
Why final yield hides rework
Final yield (sometimes called process yield) is good units out over units started: =F6/B2 gives 95.4%. It counts a unit as good whether it went through clean or needed three repairs. In this line, 175 units were reworked somewhere (=SUM(D2:D6)) and only 46 scrapped, so final yield looks healthy while the real cost, the hidden factory of rework, goes unreported.
Show final yield and RTY side by side. A big gap between them is the size of the rework problem.
Result: 0.954 shows as 95.4% with Percent format
0.954: 954 good units out of 1,000 started.
Defects per unit and DPMO
Yield counts units; defect metrics count defects, since one unit can have several. At final test, 41 defects were found on the 960 units tested (J1 and B6). Defects per unit is =J1/B6, about 0.043.
DPMO (defects per million opportunities) divides by every chance to make a defect. If each unit has 12 inspected features (J2), DPMO is =J1/(B6*J2)*1000000, about 3,559. Agree on what counts as an opportunity before you compare DPMO between lines or plants; counting more opportunities makes the number look better.
Result: 3559.0277778
3559.02...: about 3,559 defects per million opportunities.
Common mistakes
Averaging step yields
Multiply them with PRODUCT for rolled throughput yield.
Counting reworked units as first-pass passes
Record "passed after rework" in its own column; FPY uses only first-time passes.
Dividing by units started for every step
Each step's FPY divides by the units that entered that step, which shrink as scrap is removed.
Reporting final yield alone
Show it next to RTY so rework is visible.
FAQ
What is the formula for first pass yield?
FPY = units that pass the first time without rework / units entering the step. In Excel: =C2/B2, formatted as a percentage.
How do you calculate rolled throughput yield in Excel?
Multiply the first pass yields of every step: =PRODUCT(G2:G6). It's the chance a unit makes it through the whole process with no rework or scrap.
What is the difference between first pass yield and final yield?
First pass yield counts only units that were right the first time. Final yield counts all good units out, including reworked ones, so it's always equal or higher.
How do I calculate DPMO in Excel?
=defects/(units*opportunities_per_unit)*1000000. With 41 defects on 960 units and 12 opportunities each, that's about 3,559.
Is first pass yield the same as first time right?
Usually, yes: first time right (FTR) and first time through are common names for the same idea, units right the first time over units attempted. Some plants calculate FTR for the whole process instead of per step, which is closer to rolled throughput yield, so check which one a report means before comparing.
Functions used
PRODUCTMultiplies numbersSUMPRODUCTMultiply then addXLOOKUPModern lookup in any directionMINSmallest numberSUMAdds numbersAVERAGEMean of numbersMAXLargest numberCOUNTIFSCounts with several conditions
Related guides
- How to calculate scrap rate in Excel
- How to calculate OEE in Excel
- How to calculate percentage of total in Excel
- How to calculate a weighted average in Excel
- How to calculate inventory record accuracy in Excel
- How to multiply in Excel: the multiplication formula
- How to calculate absenteeism rate in Excel
Updated