Pareto() v.2

Pareto() is named after the Pareto principle that states that, for many events, roughly 80% of the effects come from 20% of the causes.

- Business management thinker Joseph M. Juran suggested the principle and named it after Italian economist Vilfredo Pareto, who observed in 1906 that 80% of the land in Italy was owned by 20% of the population.
- Pareto principle serves as a baseline for ABC-analysis, widely used in logistics and procurement for the purpose of optimizing stock of goods, as well as costs of keeping.
- Pareto charts commonly display an ordered column chart and a line chart showing the cumulated percentage on top.

I decided to combine both charts in one :

• Each segment represent the individual weight of each item within the total. 
• Segments are stacked one after the other, simulating the cumulated percentage line.  

In order to provide a reference for analysis, 2 lines can be displayed on top of the segments.
In ABC Analysis for example :

• "A class" inventory will typically contain items that account for 80% of total value, or 20% of total items. 
• "B class" inventory will have around 15% of total value, or 30% of total items. 
• "C class" inventory will account for the remaining 5%, or 50% of total items. 

The lines would materialize the A and B threshold.

Unlike the previous version of Pareto(), this release requires only one function in the upper cell (left cell if data is organized in one row x several columns) to display the entire chart.

The reference lines are now continuous and the chart is more readable.

Pareto() UDF uses the following parameters :

• Points : Range of values - Mandatory 
• ColorRange : Range of colors values. Allows one color per segment - Optional 
• Target : Percentage - Red line - Optional 
• Target2 : Percentage - Green line - Optional 
• HighlightPosition : Position - integer value - Optional 
• Legend : True / False - Display cumulated % - Optional 

- The function will identify wether the data is organized in columns or rows and draw the chart accordingly.
- ColorRange is a range containing RGB color codes (provided in the manual), so each individual segment can have a specific color. ColorRange and Points must have identical number of values.
This can be usefull to group items by family or, as show in the picture, to better visualize the A, B and C product groups.

Sparklines for Excel 2007 v. 3.6 beta available here. (Box.net might rename the file into "sparkline.xlam.zip"... so please remove the ".zip")
Demo file for Pareto available here.

PS : To provide a scale to Pareto(), use ScaleXY() ...