Excel Question

[Tris]

Anyone know a simple way of doing Pareto Charts in Excel to save me moving the dat back and forwards to Minitab?

Ta!

[Baph]

Our works "idiot's guide to Excel charts" says this (note, I haven't used Excel in years):

Sort your data in descending order by frequency of occurrence. In order to do this, select the data you want to sort (highlight category and frequency columns) and click "Data", "Sort", and then sort by "Frequency" (from drop down menu) and select "Descending".
At the bottom of the frequency column, total up the number of occurrences (e.g. =SUM(B2:B6)).
Format columns C and D so that percentages will appear when data is entered into cells for those columns. To do this, highlight columns C and D, click format, cells, and on number tab, click percentage and change the decimal places to whatever is appropriate for your use. It is your choice to determine rounding, but the total of all percentages added together, should equal 100%. For this example, we are going to round to two (2) decimal places.
In the third column, create percentages of each occurrence based on the frequency (e.g. in cell C2, type =B2/B7, for cell C3, type =B3/B7, etc.) Remember, the total of the percentages should add up to 100%.
Create a fourth column and enter the cumulative percentage (e.g. in cell D2, type: =C2. In cell D3, type (=D2 + C3), as shown in Figure 1. In cell D4, type (=D3 +C4), etc. The cumulative percentage for the last category should equal 100%. The cumulative percentage will be used to create your cumulative line on your chart.
Use the control key to select noncontiguous columns, highlight the Category, Percentage and Cumulative Percentage data. In the Figure 1 this corresponds to cells A1:A6 and C1: D6. You will not include the total of column B when selecting this data.
Generate a combination bar chart. To do this, click Insert from toolbar, then chart, or click the Chart Wizard icon from your toolbar. This will bring up the Chart Wizard. Select Custom Type and then scroll down to select Line- Column on 2 axis. You will then click the Next button at the bottom of the Chart Wizard screen. You have already highlighted your data range in Step 6. Make sure the columns tab for the "Series in" button is clicked. Then click next.
Add proper titles, labels and axis formats. Then click next. Now you must decide if you want the chart to be located as an object in the worksheet or if you would like the chart to appear as a separate worksheet.HTH.

[Captain Nemo]

:smt030 :smt030 :smt030 omg geek alert.................:smt030 :smt030 :smt030

[seedy100]

Bl00dy Hell!!!!

Someone else who uses Minitab.

Just a bit of overkill for Pareto charts though!

I find that a Real Pareto chart is wasted on 80% of the audiance, I tend to use Excel to produce a bar chart with the bars sorted into decending order.
I generally dont bother to convert actual numbers into percentages (people only convert it back in their heads anyway - and get it wrong!) and I omit the percentage line.

The method I use to do this (Its not the most efficient but it is easy to do)

1. Copy the catagoris and the occcurances ito two consectuive columns
2. Select the data in the two columns
3. Sort the data in decending order on the occurances column
4. click the chart Icon
5. Select the bar chart
6. Add titles etc
7. Job done.

I know its not a proper pareto but it does what I want and its quick!

HTH

Chris

[Tris]

Our works "idiot's guide to Excel charts" says this (note, I haven't used Excel in years):

Sort your data in descending order by ...... blah blahBAPH - Thats pretty much what I'd been doing - just very long winded and I hoped there was an easier way or somebody had written a Macro that did the job.



Just a bit of overkill for Pareto charts though!

Seedy, I agree, hence the question

I'll knock one off using your suggestion though and see if I can get away with it

Bl00dy Hell!!!!

Someone else who uses Minitab.


I know but somebody has to :cool: :cool:

Cheers both