Pareto Analysis in Excel (part 1)

Pareto principle is formulated as: 80% of the consequences (eg profit or number of rejects) comes from 20% of causes. It can be used in different sectors of human activity (eg, economy, quality of production). We will construct a graph that will show us the major causes.

Example 1

We are looking for the most common item. There are items A, B, C, D, E, F. Items can represent errors, accidents, cases,… Following table presents a list of items for the selected period. items table This is only a small part of the table. The original table has range A1:B93.

Step 1

Create a table with the frequency of items (frequency of occurrence). You can use COUNTIF function. formula of items frequency

frequency of items

If it is necessary, sort the table by frequency (from largest to smallest).

Step 2

Add a cumulative percentage column to the table.

formula for cumulative frequency

cumulative frequency

Display cumulative numbers as percentages. This is the final Pareto table. change format cells to percentage

Step 3 / create Pareto chart

Create a column chart.column chart

Change cumulative frequency data series type to Line: Right-click >> Change Series Chart Type >> Line. line in column chart beginning

Set series option to Secondary Axis: Right-click >> Format Data Series >> Secondary Axis. The line you will see is sometimes called Lorenz curve. line in column chart

Add data labels to the cumulative frequency line: Right-click >> Add Data Labels. Pareto chart

Example 2

Items can have different importance. So we have to calculate with weight of item. Let’s use the previous table.

Step 1

Create a table with the frequency of items (frequency of occurrence) and add weight of items. pareto table with weight - formula

pareto table with weight Other procedures are the same as in the previous example.

Step 2 / create Pareto chart

Create chart from the table. Select columns: Item name, frequency x weight and cumulative frequency.

Pareto chart with weight

Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress