Plotting Mean and SD on the same Line graph

**invi2012** · 04-08-2012, 06:08 AM

Hello,

I have a requirement in which I need to plot the average and the Standard Deviation (Error bar) in the same graph. I am using Excel 2010.

I am making a line graph. I am able to plot average easily but with standard deviation, I am facing problems.

For every point on the line graph, I have different standard deviation and when I select "Custom" on the Standard Deviation Options and I specify the range of SD values for each point, I get the SD lines of the same size and they are so huge that it crosses the chart itself. I am sure I can't select "Fixed", "Percentage" as each of the SD value is different corresponding to each point (average value) on the line graph.

Any help on this will be highly appreciated.

Thanks!

EDIT: Sample File Attached - As you see in the chart, the line graph is plotted based on the "Average" values, I need to plot SD on those points as well.

**mrice** · 04-08-2012, 06:10 AM

Can you supply some sample data in an attachment?

**invi2012** · 04-08-2012, 06:24 AM

Please see the attached file.

I have added sample data and created a line graph as well.

As you see in the chart, the line graph is plotted based on the "Average" values, I need to plot SD on those points as well.

How to do that?

Please help. Thanks!

**Richard Buttrey** · 04-08-2012, 06:34 AM

Hi,
Just add a second series and to avoid 'flattening' both series since the order of magnitude difference between the two scales is significant, plot the SD series on the 2nd y axis.

**invi2012** · 04-08-2012, 06:43 AM

Thanks for replying.

I cannot add another series to that graph because:

It would then not be considered as the error part but rather another data set. I need the SD to be represented on the same points as average values (just like a T / reverse T)
The values I have in the sample file is for 1 data set. In the actual file I have, I have another data set to be represented in the same graph. So, I already have 2 series in 1 graph.

Basically, I need 2 line graphs for each data set of average and each "average" point on line graph should have a SD value (represented as T/Reverse T). If I can get it for 1 data set, I can work to merge another data set into same as well. That's why I just posted help for getting it right for 1 data set as seen in sample.xlsx.

**Richard Buttrey** · 04-08-2012, 07:17 AM

Hi,

You didn't mention in your OP that you were wanting what I now assume to be error bars. That would have helped!

Just select the data series and in the Chart Tools layout section choose Error Bars more options, select Custom Specify Value, and then since all your SDs are positive choose positive values and then select the range B9:F9

**invi2012** · 04-08-2012, 07:32 AM

I am sorry, I thought I was clear in the first post. I did mention Error Bar in the first line itself of OP. Anyhow, that's fine. Thanks for helping out.

I tried what you have just mentioned there, but what I am surprised is, whether this is correct or not? If you notice carefully after doing that, all the "T" (SD Errors) appear to be of exactly same size.

Shouldn't there be a variation as there is a variation in value of each SD as well. Agreed, the variation in values is of minute scale, but that should still appear on the chard considering Y axis values are of interval of 2, and the diff between value of SD in the 1st point and 3rd point is of almost 1.5 (close to 2). But still both T are of same size?

Is there something I am missing?

Thanks for the help!

**Richard Buttrey** · 04-08-2012, 08:21 AM

Hi,

Are you sure you're not accepting the default fixed value of 2 when specifying the error bars? You should be choosing the specify value. When I do this the y scale changes to 5

And yes although they look of similar size they're not as you can demonstrate easily by temporarily adding say +20 to one of the SD formulae.