Results 1 to 3 of 3
Thread: StDev of sub range

20100108, 11:21 #1
 Join Date
 Jul 2001
 Location
 Terneuzen, Netherlands
 Posts
 895
 Thanks
 0
 Thanked 0 Times in 0 Posts
I have a range in Excel like:
A 1
A 2
B 3
A 2
C 1
A 2
B 3
etc.
In other words, different 'categories' with values.
I want to display averages and StDevs for each category. Averages are simple: =Sumif(CatRange, "A", ValueRange) / CountIf(CatRange, "A")
I'm having a little more problems doing an StDev for the categories in place. I know I should use an array formula and my starting point is:
Sqrt(Sum(xxavg)^2/(n1)
The (n1) is the COUNTIF I used above minus one. I have the average, so the SUM of the (x  xavg) is what I need now. Of course the xes used should be the xas for the category...
The array formula for that piece should look something like: {= SUM ((catrange="A") * ValueRange) }... I know I'm close (I think)... anyone who can gve me the last missing piece of information (or maybe a more elegant method of course)?

20100108, 12:33 #2
 Join Date
 Aug 2004
 Location
 Connecticut, USA
 Posts
 816
 Thanks
 0
 Thanked 0 Times in 0 Posts
You can use the array formula of:
=STDEV(IF($A$1:$A$7=D2,$B$1:$B$7)) where D2 = category
You can use the same method to find the average:
=AVERAGE(IF($A$1:$A$7=D1,$B$1:$B$7))

20100108, 12:56 #3
 Join Date
 Dec 2009
 Location
 Mexico City, D.F., Mexico
 Posts
 81
 Thanks
 0
 Thanked 0 Times in 0 Posts
There is a function that I like very much as it has never let me down, SUMPRODUCT. In your case, I propose:
=SQRT(SUMPRODUCT(($B$2:$B$26SUMIF($A$2:$A$26,C3,$B$2:$B$26)/COUNTIF($A$2:$A$26,C3))^2,($A$2:$A$26=C3)*1)/(COUNTIF($A$2:$A$26,C3)1))
$A$2:$A$26: the range with the categories a, b, c, etc.
$B$2:$B$26: the range with the values
C3: a cell containing a category, could also be an absolute value like "a"This ecopost is made of recycled electrons