Trying to create formula to give range of stan.deviations of mean

R

Roger

I am trying to create a formula in Excel that will give a mean, two standard
deviations and the range on both sides of that mean.
 
C

Conrad Carlberg

Hi Roger,

If you really want a single formula to return those four values (one mean
value, one value that represents two sd's, and two range values), you'll
need to write a user-defined function using VBA, and that function will have
to return an array of those values and be array-entered on the worksheet. If
you intend to do that frequently, a user-defined function is worth
considering.

But if you will do this just occasionally, and if you're willing to enter
four separate formulas, these would do it, assuming your raw data is in
A1:A10:

=AVERAGE(A1:A10)
=2*STDEV(A1:A10)
=ABS(AVERAGE(A1:A10)-MIN(A1:A10))
=ABS(AVERAGE(A1:A10)-MAX(A1:A10))

C^2
Conrad Carlberg
 
R

Roger

Hi Conrad,
Thanks for replying. I do, however, have a question. I'm not sure what these
two formulas are yielding. I do not believe they give a range of the mean for
data points.
=ABS(AVERAGE(A1:A10)-MIN(A1:A10))
=ABS(AVERAGE(A1:A10)-MAX(A1:A10))
Click to expand...
Below is an example of the formulas you gave and the results of the four
formulas. If you could explain the last two results which incorporate the
above formulas I would appreciate it! Thanks so much!

1 0.92
2 1
3 1.1
4 1
5 1.1
6 1
7 0.98
8 0.9
9 0.98
10 1

1.00 mean
0.129 two SD
0.098 ?
0.102 ?
 
C

Conrad Carlberg

Hi Roger,

The formulas that begin with ABS give the size of the difference
between the mean of the values and the minimum (and the maximum) values
in the data set. This was what I assumed you meant by "the range on
both sides of that mean." It seems that you meant something else. Could
you describe explicitly what you're looking for in "the range on both
sides of that mean"? The full range of values, perhaps?

C^2
Conrad Carlberg
 
M

Mangus Pyke

The formulas that begin with ABS give the size of the difference
between the mean of the values and the minimum (and the maximum) values
in the data set. This was what I assumed you meant by "the range on
both sides of that mean." It seems that you meant something else. Could
you describe explicitly what you're looking for in "the range on both
sides of that mean"? The full range of values, perhaps?
Click to expand...

ABS gives the absolute value, or distance from zero (regardless of
whether positive or negative).

Since you didn't quote anyone, I wasn't sure if this was an answer to
someone else or if you were confused about the ABS function.

MP-
 
J

joeu2004

Roger said:
I'm not sure what these two formulas are yielding.
I do not believe they give a range of the mean for data points.
=ABS(AVERAGE(A1:A10)-MIN(A1:A10))
=ABS(AVERAGE(A1:A10)-MAX(A1:A10))
Click to expand...

The problem is: you are not explaining the statistics that you
want very well.

You wrote originally:
I am trying to create a formula in Excel that will give a mean,
two standard deviations and the range on both sides of that mean.
Click to expand...

Kudos to Conrad for even understanding that to mean "two times the
standard deviation". Your subject line ("range of stan.deviations")
left me wondering.

Given that interpretation, I presume the range you are interested
in is mean +/- 2*sd. That is, "a range 2 standard deviations about
[or around] the mean".

The low end of that range is simply:

=AVERAGE(A1:A10) - 2*STDEV(A1:A10)

The high end of that range is simply:

=AVERAGE(A1:A10) + 2*STDEV(A1:A10)

By the way, the function STDEV() assumes that A1:A10 is a sample.
If A1:A10 compromise your entire "population", you will want to
use STDEVP().

If you do not know the difference between "sample" and "population"
in the context of your data, STDEV() is the better (more conservative)
function to use.
 
Top