Error bars in Excel (standard deviation, standard error, percentage)

[fog37]

TL;DR

understand error bars in excel

Hello,

I was working in excel on data reported in two columns, X data and Y data. I measured both X and Y, i.e. Y was not calculated using a function and the X data. The Y an X data was plotted as Y vs X using a scatterplot. The Excel graph offers the option to add error bars over each point in the scatterplot.

I know what the standard deviation SD is: the X and Y columns/sets have each a standard deviation which indicates the spread of values in X and the spread of values in Y relative to the mean of X and the mean of Y respectively.

The standard error SE is different from the SD: the set X is to be viewed a sample of a larger and unknown population. Given the sample mean of X, the SE indicates the spread of all the potential sample means around the actual population mean. We have calculated only one sample mean since we only a have a single sample. But the smaller the SE the better because a small SE indicates that our estimated sample mean is more likely to be close to the population mean....The same reasoning applies to the set Y. I hope I am correct.

So the SD is about the variation within a sample of data while the SE is about the means of a group of samples.

That said, the error bars in Excel can be vertical and/or horizontal and represent standard errors, standard deviations or percentage. What do the error bars (for example SD error bars) represent over each point? How should we interpret those error bars? Since all Y data has the same SD, are the vertical errors bars supposed to be the same length? Same goes for the horizontal error bars...

What would SE error bars mean over the points in the scatterplot? I am confused on how to interpret them...

Thank you!

 

[pbuk]

That said, the error bars in Excel can be vertical and/or horizontal and represent standard errors, standard deviations or percentage. What do the error bars (for example SD error bars) represent over each point?
...
What would SE error bars mean over the points in the scatterplot? I am confused on how to interpret them...

I can understand your confusion: the "Standard Deviation" and "Standard Error" options don't really do anything useful. Having said that, it is fairly easy to work out what they are showing. Why don't you try selecting the different options and viewing the results: if any are unclear you could post them here and someone might confirm your interpretations, although as I say they don't show anything useful so I wouldn't waste your time on it.

If you want to show meaningful error bars then you need to calculate them yourself and add them with the Custom option: there are many tutorials on the interweb for this ("excel custom error bars").

 

[fog37]

I can understand your confusion: the "Standard Deviation" and "Standard Error" options don't really do anything useful. Having said that, it is fairly easy to work out what they are showing. Why don't you try selecting the different options and viewing the results: if any are unclear you could post them here and someone might confirm your interpretations, although as I say they don't show anything useful so I wouldn't waste your time on it.

If you want to show meaningful error bars then you need to calculate them yourself and add them with the Custom option: there are many tutorials on the interweb for this ("excel custom error bars").

Will do thank you for the input. I can now create custom vertical error bars that represent the confidence interval CI.
My confusion is more conceptual: the confidence interval CI applies to the predicted "average" value Y. We get the predicted mean value after plotting the best fit regression curve for the X,Y data. The CI is created this way: regression predicted Y values $\pm$ margin of error ME.

The error bars should not go on the sample data points themselves, correct? But that is what Excel does: it places the error bars on the data points themselves...

Also, the CI is calculated using the margin of error ME which, in turn, is calculated using the standard error SE. That said, the SE represents the spread/standard deviation of the average value IF we collected a multitude of samples. The smaller SE, the more likely our estimated average is close to the population average. The ME is related to SE. Which one, the ME or the SE, makes more sense to describe the uncertainty of the predicted value Y for a certain X input? I am confused about that. The CI also describes how close the estimated average value is to the population value, I guess..

 

[pbuk]

The error bars should not go on the sample data points themselves, correct?
...
Which one, the ME or the SE, makes more sense to describe the uncertainty of the predicted value Y for a certain X input?

Depends, there are many ways of presenting uncertain data. What experience do you have in data science? There are lots of resources on the interweb, some good some bad. MIT's Open Courseware is often a good starting point: https://ocw.mit.edu/courses/res-6-009-how-to-process-analyze-and-visualize-data-january-iap-2012/

 

[WWGD]

Maybe this refers to residuals, i.e., in $(x_i, y_i)$, the error bars may refer to the difference between the prediction y_i^ of $y_i$ by the model, minus the actual value $y_i$.