Pellbrook Engineering's stores count A parts monthly and C parts once a year. The ABC list put seven of ten parts in class A, and AX-110, worth £31,000 a year, in class C.
| List | A parts | Share of value in A |
|---|---|---|
| Running share in part-number order | 7 of 10 | 67.0% |
| Share above each part, by value | 4 of 10 | 81.5% |
Scope.
SUM,SUMIF,RANK,IFandCOUNTIFwork in every version of Excel.MINIFSandMAXIFSin section 6 need Excel 2019 or later.ABC analysis ranks items by annual value, usually units used times unit cost. The 80% and 95% lines here are a common choice, not a rule; section 5 keeps them in cells so you can set your own.
01The Running Share Was Taken in Part Order
The list added up annual value down the sheet and divided by the total. In C2, filled to C11:
=SUM($B$2:B2)/SUM($B$2:$B$11)
Then it classed each part on that share. In D2:
=IF(C2<=0.8,"A",IF(C2<=0.95,"B","C"))
Pellbrook Engineering Stores, Annual Value by Part
Ten parts in A2:G11, in part-number order. B is annual value in pounds: units used a year times unit cost, £200,000 in all. C is the running share the old list took down the sheet, and D the class it printed. E is each part's rank by value, F the share of value held by bigger parts, and G the class from F. The A line, 80%, is in J1 and the B line, 95%, in J2.
fxCells with formulas are highlighted in green
Hover over formula cells to see the formula and highlight referenced cells
Both formulas are written correctly. The sheet was in part-number order, so the running share measured how far down the catalogue a part sat, not how much value it carried.
| Part | Annual value | Class printed |
|---|---|---|
| AX-106 | £2,000 | A |
| AX-110 | £31,000 | C |
| AX-108 | £30,000 | B |
Scenario: Enter the ten parts and values in A2:B11 in part-number order. Put =SUM($B$2:B2)/SUM($B$2:$B$11) in C2, fill it down, and check that AX-106 shows 62.5%.
02The List Has to Run Largest First
ABC analysis asks how few parts hold most of the value. That question only has an answer when the biggest parts come first, so each running share is the value held by that part and every bigger one.
Sort A2:B11 by column B, largest to smallest, with Data > Sort. Select both columns, or the values will move away from their part numbers.
| Rank | Part | Annual value | Running share |
|---|---|---|---|
| 1 | AX-102 | £58,000 | 29.0% |
| 2 | AX-105 | £44,000 | 51.0% |
| 3 | AX-110 | £31,000 | 66.5% |
| 4 | AX-108 | £30,000 | 81.5% |
| 5 | AX-104 | £12,000 | 87.5% |
On the sorted list the same SUM formula works. Four parts hold 81.5% of the value, which is what the stores needed to know.
Scenario: Select A1:B11, sort by Annual value from largest to smallest, and check that C5 now reads 81.5% on the row for AX-108.
Try it in the grid03SUMIF Gives the Share Above Without Sorting
Sorting works once. The next export arrives in part-number order again, and a sorted copy goes stale. A formula that does not depend on the row order avoids both.
For each part, add up the value of every part worth more than it. In F2, filled to F11:
=SUMIF($B$2:$B$11,">"&B2)/SUM($B$2:$B$11)
That is the running share just before the part, as if the list were sorted. AX-102, the biggest, has nothing above it and returns 0%.
| Part | Annual value | Share above |
|---|---|---|
| AX-102 | £58,000 | 0.0% |
| AX-110 | £31,000 | 51.0% |
| AX-104 | £12,000 | 81.5% |
=RANK(B2,$B$2:$B$11) in E2 shows the same order as a number, which helps when you read the sheet top to bottom.
Scenario: Leave the parts in part-number order. Enter =SUMIF($B$2:$B$11,">"&B2)/SUM($B$2:$B$11) in F2, fill it down, and check that AX-110 returns 51.0%.
04The Part That Crosses 80% Is an A
Some part always straddles the line. Classify on the share before the part, and the one that carries the total over 80% joins class A.
AX-108 starts at 66.5% and ends at 81.5%. Classed on the share after it, it falls to B, while AX-110, worth only £1,000 more, stays an A.
| Rule | AX-110, £31,000 | AX-108, £30,000 | A parts |
|---|---|---|---|
| Share after the part ≤ 80% | A | B | 3 |
| Share before the part < 80% | A | A | 4 |
Two parts of almost the same value counted on different schedules is the result the analysis exists to avoid. The share-above column from section 3 is already the share before.
Scenario: Beside the sorted list from section 2, compare =C5<=0.8 with =C4<0.8 for AX-108. Check that the first returns FALSE and the second TRUE.
05Keep the Lines in Cells
The 80% and 95% lines were typed into every formula. Put them in J1 and J2 so one change reclassifies every part.
Enter 0.8 in J1 and 0.95 in J2, formatted as percentages. In G2, filled to G11:
=IF(F2<$J$1,"A",IF(F2<$J$2,"B","C"))
| Class | Parts | Annual value | Share |
|---|---|---|---|
| A | 4 | £163,000 | 81.5% |
| B | 3 | £27,000 | 13.5% |
| C | 3 | £10,000 | 5.0% |
AX-109 has exactly 95.0% above it, so it is a C. The comparison is <, and a part that starts on the line belongs to the class below it.
Scenario: Enter 0.8 in J1 and 0.95 in J2, then =IF(F2<$J$1,"A",IF(F2<$J$2,"B","C")) in G2. Change J1 to 0.6 and check that AX-108 turns B.
06Check the Classes Before the Count Schedule
One test catches a list taken in the wrong order. Every A part must be worth at least as much as every B part:
=MINIFS(B2:B11,G2:G11,"A")>=MAXIFS(B2:B11,G2:G11,"B")
On column G it returns TRUE: the smallest A is £30,000 and the largest B £12,000. On the old column D it returns FALSE, because AX-106 at £2,000 was an A and AX-108 at £30,000 a B.
Then summarise each class with two formulas: =COUNTIF(G2:G11,"A") returns 4, and =SUMIF(G2:G11,"A",B2:B11) returns 163,000. Divide by =SUM(B2:B11) for the share.
Scenario: Enter =MINIFS(B2:B11,D2:D11,"A")>=MAXIFS(B2:B11,D2:D11,"B") against the old classes and check that it returns FALSE. Point it at column G and check for TRUE.
07Five Traps
- A running total on an unsorted list.
=SUM($B$2:B2)down part-number order measures position, not value. - Sorting one column. Sorting B alone moves the values away from their part numbers. Select the whole table.
- Classing on the share after the part. The part that crosses 80% falls to B beside a near twin in A.
- Thresholds typed into 800 formulas. Keep them in J1 and J2.
- Ranking on units instead of value. 4,000 washers at 2p are £80 a year. Multiply units by unit cost first.
08Four Exercises
- Reproduce it. Enter the ten parts in part-number order with the running share in C, and confirm that seven come out A.
- Sort it. Sort by annual value, largest first, and confirm that AX-108's running share is 81.5%.
- Skip the sort. Undo the sort, add the
SUMIFshare above in F, and confirm that AX-110 returns 51.0%. - Check it. Add the classes in G and the
MINIFS/MAXIFStest, and confirm that=COUNTIF(G2:G11,"A")returns 4.
What to Take Away
The values were right and so were the formulas. The running share was taken in an order that had nothing to do with value, so the stores counted a £2,000 part every month and a £31,000 part once a year.
Measure the share above each part with SUMIF, class on the share before the part, keep the lines in cells, and check that every A outweighs every B.