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

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 Liengme
www.stfx.ca/people/bliengme
remove caps from email


wrote in message
oups.com...
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

On Jul 13, 9:21 pm, "Bernard Liengme"
wrote:
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

wrote in message

oups.com...



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- Hide quoted text -

- Show quoted text -

Bernard,

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

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

wrote in message
oups.com...
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.

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.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

On Jul 14, 6:43 am, Stan Brown wrote:
Fri, 13 Jul 2007 22:21:24 -0300 from Bernard Liengme
:

wrote in message
roups.com...
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.
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.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

Stan,

Thanks for the input I appreciate it.