31 March 2012

Standard error of the mean (SEM) in Excel

For reasons that I don't quite understand, Excel does not have a function for calculating the standard error of the mean (SEM). Every time that I want to create a figure with error bars, I have to type in the calculation manually.

SEM is the standard deviation of the sample-mean estimate of a population mean. 

SEM is calculated by taking the standard deviation and dividing it by the square root of the sample size. In Excel, this is calculated using the following formula:


=stdev(range)/sqrt(count(range))


9 comments:

Jason A. said...

THanks!

Unknown said...

Thank you man!!

Irina Neacsu said...

I also want to thank you! I do not understand why Excel does not have this function.

Eugenia said...

It was really helpful!!! Thanks a lot

Unknown said...

This is also a very good post which I really enjoyed reading. It is not everyday that I have the possibility to see something like this..

First Canadian Aligning R&D for SR&ED Tax Credits

Dieunine said...

what is the sample size

Mark Antoniou said...

In this case, it refers to the number of values (or cells). That is what the count function is doing. E.g., count(A1:C1) would be a sample size of 3.

jdregni said...

Excel does include sqrt of (n-1) in the calculation of the STDEV. Is that not the standard error of the mean?

From this webpage
https://support.office.com/en-us/article/STDEV-function-51fecaaa-231e-4bbb-9230-33650a72c9b0

STDEV uses the following formula:

Formula sqrt(sum(x-x(bar))^2/(n-1))

where x is the sample mean AVERAGE(number1,number2,…) and n is the sample size.

Mark Antoniou said...

In Excel, STDEV is the standard deviation of the sample (calculated using n-1), whereas STDEV.P is the standard deviation of the population (calculated using n). Neither of these is the standard error of the mean.

Bafflingly, there is no function in Microsoft Excel to calculate standard error of the mean.