Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pareto Chart (Excel 97/2000)

    Hi,

    I encourage colleagues to use some statistical tools, such as experimental design. One of the visualization tools for the results is the Pareto Chart. This is a simple bar chart of effects, ordered in descending order. What I want to do is add a vertical line with the effect value corresponding to statistical significance, that is the p=0.05 line. I now do it using the graphing or drawing tools of Excel but of course, this vertical line is never located at the exact position. Moreover, changing the chart size or copying and pasting it may change its position. Is there a possibility to mix bar charts with other types so that this vertical line can be part of the chart?

  2. #2
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pareto Chart (Excel 97/2000)

    Hi Hans,

    Yes, you can create a mixed-type chart. First, make an extra column for data that will represent the threshold line. Calculate the actual threshold value and put that value into the corresponding cell for every category (that is, for every item that will appear on the X axis).

    When you create the chart, for chart type choose Custom Type (tab), then "Line - Column" in the list.

    For data series that you want to appear as columns, right-click the series, choose Format Data Series from the context menu; on the Axis tab, click Primary Axis.

    For the data series you want to represent the 0.05 threshold, choose Secondary axis. It will change to a line. You can get rid of the markers to make it look like only a line.

    See attached quicky sample.
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pareto Chart (Excel 97/2000)

    Hi Hans,

    I don't think it is possible to use built-in capabilities to do that with a bar chart. For example, I changed the overall chart type to Bar, then selected the series representing the threshold and changed that series type to Line. The line is horizontal, not vertical.

    It is possible to write a routine that will draw a horizontal line exactly where you need it, by using the X axis length (in pixels) as a scale. I did a similar thing before, if you want I can help you do this.

    JIM

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pareto Chart (Excel 97/2000)

    Thanks Jim, I just edited my previous post to add the pareto chart in the form that I prefer, but it is clearly as useful in a column layout. Thanks again.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pareto Chart (Excel 97/2000)

    <P ID="edit" class=small>Edited by Eileen on 31-Oct-01 11:14.</P>Jim,

    Thanks a lot, this is very close to what I was looking for. A Pareto Chart typically is a bar chart, not a column chart; so, although it clearly tells you the same, is there a way to rotate this chart with 90
    Attached Images Attached Images

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •