Standard Error Formula

[susan]

Excel 2007-what cells are referenced for the automatic standard error bar
calculations (using the error bars drop down on the chart tools ribbon). If
I use it (super quick and easy) to add std error bars to charts and then add
error bars the old fashioned (slow and tedious) way, I get different bars. I
have my data in a table and then a table below it with the averages of each
group. I use the average values to create a bar chart. I have another table
with std error calculated, referencing the same cells as used for the
average. For example: in the averages table I have AVERAGE(T5:X5). In the
SE table I have (STDEV(T5:X5))/SQRT(COUNT(T5:X5)). If I use the custom error
bars option and manually select the cells from the SE table I get bars that
are different from the ones I get if I just click on "error bars with
standard error".

What tipped me off is that I have 12 averages that I am charting and have
calculated SE for each. However, because some of the arrays only have one
data point, I have some SE cells that are empty. When I manually add error
bars, to the chart, I don't get any for the bars with blank SE cells, even
though I select them as part of the reference. If I do the automatic add of
error bars I get bars added where there shouldn't be any. I just can't
figure out what cell(s) it is getting the data from to chart the bars.

 

[Mike Middleton]

susan -

I don't have an answer, but you should look at Jon Peltier's blog

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

especially the comments at the bottom of that web page.

I also do not know how Excel determines standard error for chart error bars.
I cannot investigate immediately. If I had time, I first would check to see
if they are based on calculations like the STEYX worksheet function.

- Mike
http://www.MikeMiddleton.com


Excel 2007-what cells are referenced for the automatic standard error bar
calculations (using the error bars drop down on the chart tools ribbon). If
I use it (super quick and easy) to add std error bars to charts and then add
error bars the old fashioned (slow and tedious) way, I get different bars.
I
have my data in a table and then a table below it with the averages of each
group. I use the average values to create a bar chart. I have another
table
with std error calculated, referencing the same cells as used for the
average. For example: in the averages table I have AVERAGE(T5:X5). In the
SE table I have (STDEV(T5:X5))/SQRT(COUNT(T5:X5)). If I use the custom
error
bars option and manually select the cells from the SE table I get bars that
are different from the ones I get if I just click on "error bars with
standard error".

What tipped me off is that I have 12 averages that I am charting and have
calculated SE for each. However, because some of the arrays only have one
data point, I have some SE cells that are empty. When I manually add error
bars, to the chart, I don't get any for the bars with blank SE cells, even
though I select them as part of the reference. If I do the automatic add of
error bars I get bars added where there shouldn't be any. I just can't
figure out what cell(s) it is getting the data from to chart the bars.