How many Standard Deviations above and below the mean each number is.

[pkaraffa]

I have a list of numbers in A1:A20 that has a STDEV of 27.6. I need a
formula that I can copy down that will tell me how many STDEV's above
and below each number is. Thanks in advance PJ.


13
123
24
35
35
46
46
57
57
58
6
79
79
79
78
57
46
35
24
32

 

[Bernard Liengme]

Assuming by "how many STDEV's above and below each number is", you mean how
many STDEV above/below the average, use
=(A1-AVERAGE($A$1:$A$20))/STDEV($A$1:$A$20)
best wishes

 

[pkaraffa]

Assuming by "how many STDEV's above and below each number is", you mean how
many STDEV above/below the average, use
=(A1-AVERAGE($A$1:$A$20))/STDEV($A$1:$A$20)
best wishes
--
Bernard V Liengmewww.stfx.ca/people/bliengme
remove caps from email







- Show quoted text -

Bernard,

Thanks you very much. That is exactly what I was looking for.

 

[Stan Brown]

Fri, 13 Jul 2007 22:21:24 -0300 from Bernard Liengme

Assuming by "how many STDEV's above and below each number is", you mean how
many STDEV above/below the average, use
=(A1-AVERAGE($A$1:$A$20))/STDEV($A$1:$A$20)

Or use the STANDARDIZE function:

=standardize(A1, average(A$1:A$20), stdev(A$1:A$20))

Like Bernard, I would not hard-code the 27.6 because if any of the
numbers in column A change you'd have to remember to recompute the
standard deviation. This way it's autmatic.

 

[pkaraffa]

Fri, 13 Jul 2007 22:21:24 -0300 from Bernard Liengme


Or use the STANDARDIZE function:

=standardize(A1, average(A$1:A$20), stdev(A$1:A$20))

Like Bernard, I would not hard-code the 27.6 because if any of the
numbers in column A change you'd have to remember to recompute the
standard deviation. This way it's autmatic.

Stan,

Thanks for the input I appreciate it.