1. 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(x-xavg)^2/(n-1)

The (n-1) 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 x-es used should be the x-as 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)?

2. 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))

3. 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\$26-SUMIF(\$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"

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•