Cellwise Start free

Excel guide · 4 min read

How to calculate scrap rate in Excel

Quick answerScrap rate is scrap units divided by total units started (or produced, scrap included). For a whole log it's =SUM(F2:F10)/SUM(E2:E10), formatted as a percentage. For one line or shift, use SUMIFS on both parts: =SUMIFS(F2:F10,B2:B10,"L1")/SUMIFS(E2:E10,B2:B10,"L1").

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGHIJK
1DateLineShiftItemUnits startedScrap unitsReasonScrap costItemStd cost
2Oct 5, 2026L1DayWID-A120030Setup126WID-A4.2
3Oct 5, 2026L2NightWID-B80044Material345.4WID-B7.85
4Oct 12, 2026L1NightWID-A115052Machine218.4
5Oct 12, 2026L2DayWID-B90018Setup141.3
6Oct 19, 2026L1DayWID-A125025Operator105
7Oct 26, 2026L2NightWID-B78061Material478.85
8Nov 2, 2026L1DayWID-A118021Setup88.2
9Nov 2, 2026L2DayWID-B86039Material306.15
10Nov 9, 2026L1NightWID-A110048Machine201.6

Download this sheet as an Excel practice file (.xlsx)

The basic scrap rate

Keep a production log with one row per run: date, line, shift, item, units started in E, scrap units in F and the main scrap reason in G. Units started means everything that went into the process, good and bad. If parts are scrapped for being out of tolerance, Cpk tells you whether the process can hold the spec at all.

Overall scrap rate is =SUM(F2:F10)/SUM(E2:E10). Format it as a percentage with Home > Number > Percent Style, then add a decimal with Increase Decimal, since scrap rates live in the low single digits and 4% hides the difference between 3.6% and 4.4%.

=SUM(F2:F10)/SUM(E2:E10)

Result: 0.036659436009 shows as 3.67% with Percent format

0.0367: 338 scrapped out of 9,220 started, a 3.67% scrap rate.

Use units started as the denominator, not good units

A common shortcut divides scrap by good units, because good units are what the ERP reports as "produced". That overstates the rate and makes it impossible to add up: the denominator shrinks as scrap grows.

If your log only has good units, rebuild started as good + scrap. And don't average the scrap % of each row: =AVERAGE(F2:F10/E2:E10) gives every run the same weight, so a short 780-unit run counts as much as a 1,250-unit one. Always sum the scrap and the units first, then divide.

=SUM(F2:F10)/(SUM(E2:E10)-SUM(F2:F10))

Result: 0.038054492231 shows as 3.81% with Percent format

0.0381: 3.81% when scrap is divided by good units. The average of the row percentages gives a third answer, 3.89%. The correct rate is 3.67%.

Scrap rate by line, shift and month with SUMIFS

Put the same criteria in both SUMIFS so the scrap and the units cover exactly the same runs. By line: =SUMIFS(F2:F10,B2:B10,"L1")/SUMIFS(E2:E10,B2:B10,"L1") is 2.99% for L1, and the same with "L2" gives 4.85%. By shift, Night runs at 5.35% against 2.47% for Day.

Add a criteria pair to drill down: line L1 on nights alone is 4.44%, so the night problem isn't only an L2 problem. For months, use a date range so the formula keeps working next year: =SUMIFS(F2:F10,A2:A10,">="&DATE(2026,10,1),A2:A10,"<"&DATE(2026,11,1)) for October scrap, and the same criteria on column E for October units. October comes to 3.78%, November so far 3.44%.

In practice, put the line, shift and month in cells and point the criteria at them, so one formula fills a whole summary table.

=SUMIFS(F2:F10,C2:C10,"Night")/SUMIFS(E2:E10,C2:C10,"Night")

Result: 0.053524804178 shows as 5.35% with Percent format

0.0535: night shift scraps 5.35% of what it starts, more than double the day shift.

Scrap cost with a standard cost lookup

Units hide the money. A scrapped WID-B costs 7.85 at standard; a WID-A costs 4.20. Keep standard costs in a small table (J:K) and price each row in H2 with =F2*XLOOKUP(D2,$J$2:$J$3,$K$2:$K$3). In Excel 2019 or earlier, use VLOOKUP(D2,$J$2:$K$3,2,FALSE) instead of XLOOKUP. To build a scrap allowance into a product's cost and order quantities, use the free bill of materials template.

Use the standard cost from your costing system, not the selling price. Scrap cost tells you what the scrap used up in material and labour; lost margin is a separate question.

=SUM(H2:H10)

Result: 2010.9

2010.9: total scrap cost at standard. L2's WID-B accounts for 1,271.70 of it even though it scrapped fewer units than L1.

Top scrap reasons, ranked by cost

In Excel 365, list each reason once and sort by cost in one formula: =SORTBY(UNIQUE(G2:G10),SUMIFS(H2:H10,G2:G10,UNIQUE(G2:G10)),-1). The SUMIFS inside gets the cost of each unique reason, and SORTBY orders the reasons by it, largest first.

Rank by cost, not just by count. Material problems are 144 of the 338 scrapped units (42.6%) but 1,130.40 of the cost (56.2%), because they hit the expensive item. Machine (100 units, 420), Setup (69 units, 355.50) and Operator (25 units, 105) follow. In older Excel, use a PivotTable with Reason in Rows and Scrap cost in Values, then sort largest to smallest.

=SORTBY(UNIQUE(G2:G10),SUMIFS(H2:H10,G2:G10,UNIQUE(G2:G10)),-1)

Result: spills 4 rows × 1 column

Material
Machine
Setup
Operator

Material, Machine, Setup, Operator: the scrap reasons from most to least expensive.

Common mistakes

Dividing scrap by good units

Divide by units started (good + scrap), so the rate can't exceed 100% and adds up across lines.

Averaging the scrap % of each run

Sum scrap and units separately with SUMIFS, then divide once.

Ranking scrap reasons by units only

Add a cost column with a standard cost lookup and rank by cost too.

Different criteria in the top and bottom SUMIFS

Copy the criteria pairs exactly, or point both at the same criteria cells.

FAQ

What is the formula for scrap rate?

Scrap rate = scrap units / total units started x 100. In Excel: =SUM(F2:F10)/SUM(E2:E10) with the cell formatted as a percentage, so you don't need the x 100.

How do I calculate scrap percentage by month in Excel?

Use SUMIFS with a date range on both scrap and units: =SUMIFS(F:F,A:A,">="&DATE(2026,10,1),A:A,"<"&DATE(2026,11,1)) divided by the same SUMIFS on the units column.

What is the difference between scrap rate and yield?

They're two sides of the same count: if units started is the base, yield is good units / units started and scrap rate is scrap / units started. Rework complicates it; first pass yield counts reworked units as not passing the first time.

How do I calculate the cost of scrap in Excel?

Multiply scrap units by the item's standard cost, looked up from a cost table: =F2*XLOOKUP(D2,$J$2:$J$3,$K$2:$K$3). Then total or SUMIFS the cost column.

Functions used

Related guides

Updated