Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Trying to create formula to give range of stan.deviations of mean
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. |
#2
|
|||
|
|||
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 "Roger" wrote in message ... 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. |
#3
|
|||
|
|||
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)) 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 ? "Conrad Carlberg" wrote: 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 "Roger" wrote in message ... 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. |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
On 17 Jul 2005 08:12:54 -0700, "Conrad Carlberg"
wrote: 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? 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- -- "Learning is a behavior that results from consequences." B.F. Skinner |
#6
|
|||
|
|||
Roger wrote:
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)) 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. 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
create range bar graph | Charts and Charting in Excel | |||
Using the text from a cell as a range name in a formula | Excel Discussion (Misc queries) | |||
Can a formula check for a certain value in a range? | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
how do I make a formula NOT change when the data range is moved? | Excel Discussion (Misc queries) |