01 / 09The Running Share Was Taken in Part Order

ABC AnalysisRunning TotalsData Analysis

ABC Analysis in Excel: Classify Items by Cumulative Share

The Stores Counted Seven Parts Every Month and the Third Most Valuable Once a Year, Because the ABC Running Share Was Taken in Part-Number Order

16 Nov 20267 min read

UsesSUMIFRANKIFSUM

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.

ListA partsShare of value in A
Running share in part-number order7 of 1067.0%
Share above each part, by value4 of 1081.5%

Scope. SUM, SUMIF, RANK, IF and COUNTIF work in every version of Excel. MINIFS and MAXIFS in 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.

ABCDEFG
1
Part
Annual value
Running % printed
Class printed
Rank
Share above
Class true
2
AX-101
6000
3.0%
A
7
92.0%
B
3
AX-102
58000
32.0%
A
1
0.0%
A
4
AX-103
3000
33.5%
A
9
97.5%
C
5
AX-104
12000
39.5%
A
5
81.5%
B
6
AX-105
44000
61.5%
A
2
29.0%
A
7
AX-106
2000
62.5%
A
10
99.0%
C
8
AX-107
9000
67.0%
A
6
87.5%
B
9
AX-108
30000
82.0%
B
4
66.5%
A
10
AX-109
5000
84.5%
B
8
95.0%
C
11
AX-110
31000
100.0%
C
3
51.0%
A

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.

PartAnnual valueClass printed
AX-106£2,000A
AX-110£31,000C
AX-108£30,000B

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%.

Try it in the grid

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.

RankPartAnnual valueRunning share
1AX-102£58,00029.0%
2AX-105£44,00051.0%
3AX-110£31,00066.5%
4AX-108£30,00081.5%
5AX-104£12,00087.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 grid

03SUMIF 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%.

PartAnnual valueShare above
AX-102£58,0000.0%
AX-110£31,00051.0%
AX-104£12,00081.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%.

Try it in the grid

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.

RuleAX-110, £31,000AX-108, £30,000A parts
Share after the part ≤ 80%AB3
Share before the part < 80%AA4

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.

Try it in the grid

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"))
ClassPartsAnnual valueShare
A4£163,00081.5%
B3£27,00013.5%
C3£10,0005.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.

Try it in the grid

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.

Try it in the grid

07Five Traps

  1. A running total on an unsorted list. =SUM($B$2:B2) down part-number order measures position, not value.
  2. Sorting one column. Sorting B alone moves the values away from their part numbers. Select the whole table.
  3. Classing on the share after the part. The part that crosses 80% falls to B beside a near twin in A.
  4. Thresholds typed into 800 formulas. Keep them in J1 and J2.
  5. Ranking on units instead of value. 4,000 washers at 2p are £80 a year. Multiply units by unit cost first.

08Four Exercises

  1. Reproduce it. Enter the ten parts in part-number order with the running share in C, and confirm that seven come out A.
  2. Sort it. Sort by annual value, largest first, and confirm that AX-108's running share is 81.5%.
  3. Skip the sort. Undo the sort, add the SUMIF share above in F, and confirm that AX-110 returns 51.0%.
  4. Check it. Add the classes in G and the MINIFS/MAXIFS test, 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.

Share this article:
Back to Blog
Daily challenge · Day 107

Total a Mileage Claim for One Pay Period with SUMIFS

A new exercise every day, solved in a real grid.

Solve today’s challenge