# Plotting Reliability Charts

1. ## 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. ## Re: Plotting Reliability Charts

Welcome to the forum.

See attached.

3. ## 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. ## Re: Plotting Reliability Charts

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

5. ## 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.

6. ## 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. ## 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. ## 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. ## 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.

10. ## Re: Plotting Reliability Charts

Thank you, Andy.

11. ## 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!

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