# Statistics question

**Catfive Lander**Oct 10, 2012 6:17 PM

I can easily calculate the standard deviation for some rows with the STDDEV function. But that returns me a value expressed in the same units as the things being measured. (In other words, if I have times of 1,12,6,11 and 8 seconds, I will be told that the average time is 7.6 seconds, and the standard deviation is 4.39 seconds).

What I'd like to be able to do is to say that the record with value 1 is (say) 3 std deviations away from the average. Or that the 6 second row is within 1 standard deviation.

Put it the other way around: I am trying to find records which are more than 3 standard deviations of the mean, because they are the outliers I am interested in.

I cannot see how to convert a STDDEV result into being a "number of standard deviations".

(If I have not expressed myself clearly, I'm using this sort of stuff http://en.wikipedia.org/wiki/68-95-99.7_rule and I'm trying to find >3-sigma records)

Edit: I may be being stupid on this, but is it correct simply to multiply the STDDEV result by 1, 2, 3 and so on, and then compare to the time? That is, is it OK to do the following:

Mean = 7.6 seconds (from my sample data mentioned in the above post)

StdDev = 4.29 seconds

So 2sigma would be 4.29*2 = 8.78 seconds, plus or minus the mean?

So any record with a time value between -1.18 (7.6-8.78) and 16.38 (7.6+8.78) seconds would be within 2-sigma, assuming normal distribution?

I am still wondering if there's a nice function or something that returns the sigma value for any given set of records?

What I'd like to be able to do is to say that the record with value 1 is (say) 3 std deviations away from the average. Or that the 6 second row is within 1 standard deviation.

Put it the other way around: I am trying to find records which are more than 3 standard deviations of the mean, because they are the outliers I am interested in.

I cannot see how to convert a STDDEV result into being a "number of standard deviations".

(If I have not expressed myself clearly, I'm using this sort of stuff http://en.wikipedia.org/wiki/68-95-99.7_rule and I'm trying to find >3-sigma records)

Edit: I may be being stupid on this, but is it correct simply to multiply the STDDEV result by 1, 2, 3 and so on, and then compare to the time? That is, is it OK to do the following:

Mean = 7.6 seconds (from my sample data mentioned in the above post)

StdDev = 4.29 seconds

So 2sigma would be 4.29*2 = 8.78 seconds, plus or minus the mean?

So any record with a time value between -1.18 (7.6-8.78) and 16.38 (7.6+8.78) seconds would be within 2-sigma, assuming normal distribution?

I am still wondering if there's a nice function or something that returns the sigma value for any given set of records?

- 152 Views
- Tags: none (add)