Pareto analysis (80/20)
Advanced Data AnalystThe Pareto principle is one of the most useful frameworks in quality management. Roughly 80% of outcomes come from 20% of causes. By ranking defect categories, building cumulative totals, and classifying each row by where it falls in the distribution, you can quickly identify which problems deserve the most attention.
Your quality team has recorded 490 defects across 8 categories over the past quarter. The data is already sorted from highest to lowest frequency.
Build the complete Pareto analysis by filling in columns C through F:
- Rank (column C): Assign a numeric rank to each defect category based on its count. The highest-count category should receive rank 1.
- Cumulative count (column D): Calculate the running total of defect counts from the top of the list down.
- Cumulative % (column E): Express the cumulative count as a proportion of all 490 defects.
- 80/20 class (column F): Label each row "Top 80%" if it falls within the first 80% of cumulative defects, or "Remaining 20%" otherwise.
Think carefully about your mixed references. Some need to stay fixed, while others should expand as you copy formulas down the table.
Need some help?
Hint 1
RANK takes a value, a reference range, and an order argument. The reference range needs to stay fixed when you copy the formula down.
Hint 2
The cumulative count uses an expanding SUM range: lock the start of the range with an absolute reference ($B$2) but leave the end relative (B2, B3, B4...) so it expands as you copy down.
Hint 3
For the cumulative %, divide the cumulative count by the total using SUM($B$2:$B$9) as the denominator. Lock that entire range. Then in column F, your IF threshold is 0.8, not 80.
Pareto analysis (80/20)
Advanced Data AnalystThe Pareto principle is one of the most useful frameworks in quality management. Roughly 80% of outcomes come from 20% of causes. By ranking defect categories, building cumulative totals, and classifying each row by where it falls in the distribution, you can quickly identify which problems deserve the most attention.
Your quality team has recorded 490 defects across 8 categories over the past quarter. The data is already sorted from highest to lowest frequency.
Build the complete Pareto analysis by filling in columns C through F:
- Rank (column C): Assign a numeric rank to each defect category based on its count. The highest-count category should receive rank 1.
- Cumulative count (column D): Calculate the running total of defect counts from the top of the list down.
- Cumulative % (column E): Express the cumulative count as a proportion of all 490 defects.
- 80/20 class (column F): Label each row "Top 80%" if it falls within the first 80% of cumulative defects, or "Remaining 20%" otherwise.
Think carefully about your mixed references. Some need to stay fixed, while others should expand as you copy formulas down the table.
Need some help?
Hint 1
RANK takes a value, a reference range, and an order argument. The reference range needs to stay fixed when you copy the formula down.
Hint 2
The cumulative count uses an expanding SUM range: lock the start of the range with an absolute reference ($B$2) but leave the end relative (B2, B3, B4...) so it expands as you copy down.
Hint 3
For the cumulative %, divide the cumulative count by the total using SUM($B$2:$B$9) as the denominator. Lock that entire range. Then in column F, your IF threshold is 0.8, not 80.