Excel Chart Type? 1

[shelby55]

Hi

I am using Excel 2003.

I have data showing a value, upper confidence level and lower confidence level. I want to show all 3 in a graph...is this called a box and whisker graph?

If so, how do I create in Excel and if not, please advise what chart type to choose. Thanks.

 

[BlueHorizon]

Hi shelby55,

Here's a tutorial from Microsoft:

Article ID: 155130 - Last Review: July 2, 2010 - Revision: 4.0
How to create a BoxPlot/Box and Whisker Chart in Excel

http://support.microsoft.com/kb/155130

HTH,


Best,
Blue Horizon

 

[shelby55]

HI

Thanks so much for replying. I worked on this all afternoon prior to posting and once I posted a query I figured it out. I only have 3 data points so I was able to use the stock chart. I figured out that the error message I was getting simply meant I had to put the 3 figures in order from high to low (UCL, value, LCL).

The big thing was that I couldn't make it look like I wanted i.e. a square in the middle and the others as a horizontal line but finally, I figured out how to select per series and then I could format as I wanted.

I really appreciate the link you gave me because maybe there is something that will enhance this further though I'm starting to wonder if what I am describing is truly "box and whisker"....thanks again!

 

[JonPeltier]

Here's another approach.

Put your X values or labels into a column, and your Y values into the next column. Use a formula to calculate the (UCL minus value) for each point into the third column, and another formula to calculate (value - LCL) into the fourth column.

Make a chart with the first two columns. Line chart or XY chart depending on the X values. Format as a square, with connecting lines if desired.

Then add custom error bars using the third column as positive error bars and the fourth column as negative error bars.

To add custom error bars, read my article here:

http://peltiertech.com/WordPress/custom-error-bars-in-excel-charts/

 

[shelby55]

Hi Jon

I actually came across your website when I was googling on how to do this but I didn't understand the approach described.

I also don't understand the confidence function in Excel i.e. how do I translate this:

=(C2+1)/D2*(1-(1/(9*(C2+1)))+1.96/(3*SQRT(C2+1)))^3*100

which is the confidence level algorithm used in the work I'm doing to the Excel function.

I also wanted to add a line for 100 which is the average mark on this graph but couldn't figure out how to do it.

Note also that I created named ranges for the various values because this is a dynamic workbook where data will change. Does any of this preclude me from being able to use your method?

Thanks.

 

[JonPeltier]

I am not familiar with your function. In any case, you have X and Y values, presumably this function helps you determine UCL and LCL, and you can then determine how large the positive and negative error bars must be.

You can name your ranges for the error bar values the same as you can for the X and Y values, and use them in the chart. The X and Y names you set through the Source Data dialog. The error bar names are set through the dialog that applies custom ranges to your error bar values.

(Using Excel's dialog, the error bars will be linked to the dynamic names. I just noticed that my handy little utility does not incorporate the names into the error bar data, just the addresses of the named ranges. I'll look into this.)

Add your Average line using one of these approaches:

http://peltiertech.com/Excel/Charts/AddLine.html

 

[lionelhill]

if you do a lot of this, you might like to consider a 3rd party graph package, some of which interact with Excel well, but vastly extend its plotting capabilities.