Standard Deviation in Excel

whuppins

I'm much more familiar with Excel than I am with statistics, but I just noticed that asking Excel to calculate the standard deviation on a single-value array causes a #DIV/0! error. Since when does calculating the standard deviation of a single value involve dividing by zero? Don't you end up taking the square root of 0/1 = 0?

Rook

whuppins wrote: »

I'm much more familiar with Excel than I am with statistics, but I just noticed that asking Excel to calculate the standard deviation on a single-value array causes a #DIV/0! error. Since when does calculating the standard deviation of a single value involve dividing by zero? Don't you end up taking the square root of 0/1 = 0?

My first guess would be that it uses n-1 rather than n. Either way, finding the deviation of a single value is tres dumb.

edit: Try stdevp though, if you're desperate. stdev is for samples and stdevp is for populations.

whuppins

Obviously standard deviation is of little value with a single sample, but "tres dumb"? Au contraire!

The worksheet is actually calculating the standard deviation for a range of values, the size of which can vary each time it's used. In some cases, that size can even be 1. I just wanted to see why I had to write additional logic into the formula just to handle this special case. stdevp() doesn't work because in 99% of cases I will be working with more common array sizes. Any other ideas?

Rook

Why not just use STDEVP as it sounds like that's what you thought you were doing in the first place judging from your initial calculation.

whuppins

You're right; I had previously misunderstood the difference between the two functions. STDEVP() is the more appropriate function for what I'm trying to do.

Thanks!