Can you have both average and standard deviation in the same cell?

  • Thread starter Thread starter Andy Zhao
  • Start date Start date
A

Andy Zhao

Or, if I want to make a chart which will show both the
average and stardard deviation of mutiple samples in a bar
chart graph(e.g., data like below column A and B), how
should I do that?

Thanks for the help!

Andy

A B Average STDEV
1.0 1.0 1.0 0.0
0.5 0.7 0.6 0.1
0.6 0.5 0.5 0.0
0.5 0.9 0.7 0.2
1.5 1.0 1.3 0.2
2.4 1.0 1.7 0.7
0.6 2.9 1.7 1.2
2.0 1.4 1.7 0.3
1.2 1.8 1.5 0.3
0.9 2.0 1.4 0.6
1.6 3.0 2.3 0.7
5.8 3.4 4.6 1.2
 
[This followup was posted to microsoft.public.excel.charting with an
email copy to Andy Zhao.
Please use the newsgroup for further discussion.]

First, the standard deviation values are wrong. The correct values
are:

0.14
0.07
0.28
0.35
0.99
1.63
0.42
0.42
0.78
0.99
1.70

It appears that the posted std. dev. values include the 2 data points
*and* the average value!

That said...

Do you have a bar chart as XL defines it? Or is it a column chart? In
either case, I don't know what it means to add the average and std.dev.
values to the chart. Do you want a line chart for the average? If so,
just select the column and add it to the chart. Then, select the new
series and with it selected, select Chart | Chart Type... | and select
the desired line chart.

If you want to plot mean +/- std.dev. introduce 2 new columns. In one
calculate the mean + std.dev. value, in the other the - value. Plot
both columns and follow the steps in the para above.

--
Trouble finding replies to your posts? Use a newsreader. See the
tutorial 'Outlook Express and Newsgroups' on my web site

Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
It appears that the posted std. dev. values include the 2 data points
*and* the average value!
Click to expand...


The OP's displayed STDEV values are not consitent with this hypothesis
either. The issue appears to be that the displayed values in columns A
and B are rounded, so too few figures are given in the STDEV column to
distinguish between STDEV(A1:B1), STDEVP(A1:B1), STDEV(A1:C1), or
STDEVP(A1:C1)
 
Andy said:
# A B C Average STDEV
1 2.3 3.1 1.8 2.1 0.7
2 3.6 2.8 2.3 2.7 0.7
3 2.3 2.7 3.5 2.9 0.6
4 5.3 4.4 4.6 4.6 0.5
5 6.2 5.3 3.7 5.1 1.3
6 2.2 3.2 3.3 3.7 0.6
7 3.7 4.3 4.8 5.0 0.6
8 8.8 6.5 6.8 7.5 1.3
9 3.8 2.9 4.3 5.0 0.7
10 2.7 2.6 2.8 4.5 0.1

Now, I'd like to plot these average values in a column or
bar chart, in which the STDEV can also be viewed directly.
Click to expand...

I would add two columns: average + STDEV and Average - STDEV. Now select
the average column and the two new columns (total of 3 columns). Hit the
chart wizard button, make a clustered column chart.

Now select a series and do Format | Selected data series | Series order tab.
Put the biggest one at the top (AVG+STDEV) and the smallest one at the
bottom (AVG-STDEV). Then go to the Options tab and set Overlap to 100.

Does that work for you? You can mess around with the settings of each data
series if you want the bar to look different.

Dave
dvt at psu dot edu
 
Back
Top