+ Reply to Thread
Results 1 to 11 of 11

Plotting Reliability Charts

  1. #1
    Registered User
    Join Date
    10-28-2008
    Location
    New Jersey
    MS-Off Ver
    2007
    Posts
    15

    Plotting Reliability Charts

    How would I do a (clustered column) reliability chart in Excel? (The values 90%, 99%, 99.9%, 99.99%, 99.999%, etc. must be equally spaced along the Y-axis -- otherwise, it is not possible to distinguish between 99.95% and 99.9997%, for example.)

    Sample data:
    Equip Run 1 Run2 Run3
    Equip1 93.4854% 94.5433% 93.9843%
    Equip2 99.9942% 99.9989% 99.9953%
    Equip3 99.9439% 99.9753% 99.9488%
    Equip4 99.9993% 99.9994% 99.9994%
    Equip5 99.9985% 99.9982% 99.9993%

    What I don't want is for 93.5% bars to be by themselves and all the others clustered up near 100% (as happens when you use a linear chart).

    Thanks.

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,663

    Re: Plotting Reliability Charts

    Welcome to the forum.

    See attached.
    Attached Files Attached Files
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    10-28-2008
    Location
    New Jersey
    MS-Off Ver
    2007
    Posts
    15

    Re: Plotting Reliability Charts

    Thanks shg for your help! This is similar to what I ended up doing, except I used Text Boxes to put "90%", "99%", "99.9%", etc. next to the Y-axis.

    Is there a (cleaner) way to directly change the displayed Y-axis values or adjust the scale?

    Thank again.

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,663

    Re: Plotting Reliability Charts

    Using data labels is very different from using text boxes. What happens if you resize the chart?

    I don't understand what additional question you're asking.

  5. #5
    Registered User
    Join Date
    10-28-2008
    Location
    New Jersey
    MS-Off Ver
    2007
    Posts
    15

    Re: Plotting Reliability Charts

    Yes, you are absolutely correct; there are problems with using text boxes.

    When I use your method, the "labels" do not line up correctly (see attached upper right).

    To clarify my question. Is there a way to either:
    1) Change the axis itself (similar to log scale) where the spacing between tick marks changes (e.g., attached lower right, except with the correct values), or
    2) Use a function to assign the axis values themselves (e.g., setting Y-axis = 1 - value, in attached lower right)?

    Thanks.
    Attached Files Attached Files

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,663

    Re: Plotting Reliability Charts

    Look at my formula in J4 and your formula in J35.

    Also, your y axis scale is 0 to 1, and mine is -6 to 0.

  7. #7
    Registered User
    Join Date
    10-28-2008
    Location
    New Jersey
    MS-Off Ver
    2007
    Posts
    15

    Re: Plotting Reliability Charts

    shg, I think you were looking at the wrong chart.

    I used your values (J4:L8) to generate the upper right chart (cells O1:Z20). The labels here do not line up with the Y-axis. (My attempt at using your method)

    I used a different calculation in cells J35:L39 to generate the lower chart (cells N26:T45) to try to clarify the additional question that you said you did not understand:

    > To clarify my question. Is there a way to either:
    > 1) Change the axis itself (similar to log scale) where the spacing between tick marks changes (e.g., attached lower right, except with the correct values), or
    > 2) Use a function to assign the axis values themselves (e.g., setting Y-axis = 1 - value, in attached lower right)?

    Thanks.

  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,663

    Re: Plotting Reliability Charts

    The difference in label alignment seems to be a difference between Excel 2003 and 2007, and I don't have a suggestion for how to fix it. If Andy Pope stops by, he will.

    I still don't understand your second question, sorry. What does my chart not do that you want to do? Bear in mind that 'more 9s' is only exponential in the sense of 1-10^x.

  9. #9
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,890

    Re: Plotting Reliability Charts

    If you change the X value for the 4th series to 0 in range B4:B10 and then move the series to the secondary axis.

    Apply secondary horizontal axis.
    Format the secondary axis so the value run zero to 99 in the right direction.
    Also fix the Max value of secondary vertical axis.

    Create some space on the left by moving the plot area.

    Finally format the secondary axes so they are not shown, currently gray so you can see what I did.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  10. #10
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,663

    Re: Plotting Reliability Charts

    Thank you, Andy.

  11. #11
    Registered User
    Join Date
    10-28-2008
    Location
    New Jersey
    MS-Off Ver
    2007
    Posts
    15

    Re: Plotting Reliability Charts

    Andy, shg, Thank you for the fix to the alignment issue.

    I'm sorry my 2nd question was so unclear. Since I would be generating lots of these charts, I was basically exploring simpler alternate methods. For example, perhaps there was a method of keeping the Y-axis values instead of "hiding them" with data labels (or text boxes in my original solution). You can disregard the question.

    Thanks again for all your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1