Standard Deviation of Groups in List

[mbwd]

I have a big dataset, and I am trying to determine the standard deviation of separate groups. The groups are in a single column, for simplicity sake let's say they are just "A" and "B." The scores are in a second column. I want to have a single forumla that I can paste into a "Group Standard Deviation" column that will discern whether the group column has a value of "A" or "B."

I tried the suggestion here, also demonstrated here:

Please Login or Register  to view this content.

ctr+shift+enter

In my case:

Please Login or Register  to view this content.

ctr+shift+enter

But I am getting a "#DIV/0!" error.

I think I am getting the error becuase my groups are differentiated in the same column (A2:A21), so I only have RangeX and RangeX. It appears that this approach only works if RangeX is different than RangeY.

Is there a way to do this? Below is sample data.

Thanks!

ColumnA ColumnB
A 1
A 2
A 3
A 4
A 5
A 6
A 7
A 8
A 9
A 10
B 10
B 20
B 30
B 40
B 50
B 60
B 70
B 80
B 90
B 100

[shg]

Welcome to the forum.

I can't tell what you're trying to do, but no values survive that test because no column A value can't be both "A" and "B". Watch the formula evaluate.

If you want the SD of the A group,

=STDEV(IF(A2:A21="A", B2:B21))

[mbwd]

Thanks for the reply. Sorry to be unclear.

I am trying to enter a formula into one new column, that I can paste down the whole column, that will calculate the standard deviation for the values in Column B only of the rows where there is a specified value of Column A (i.e., "A", then "B", etc. etc.).

So, for example, if Column A = A, the formula would generate a standard deviation for the values in Column B -- but only for B2:B11. And when Column A = B, it would calcluate the standard deviation for the values in Column B -- but only for B12:B21.

Essentially, I want a single column with standard deviations in Column C for each subgroup listed in Column A.

I hope that makes sense. And it is possible. . . .

[shg]

This is still a muddle to me. Maybe in C2 and copy down,

=STDEV(IF(A$2:A$21=A2, B$2:B$21))

But all of those formulas are going to give one of two answers.

[mbwd]

Again, apologies -- I must be having a hard time making this clear. Here is what I want the outcome to look like:

Please Login or Register  to view this content.

Column C gives the standard deviation of Column B, but by the subgroups in Column A. So when Column A = "A", the standard deviation is calcluated only for A2:A11 and is 3.027. When Column A = "B", the standard deviation is calcluated only for A12:A21, and is 30.276.

Is that possible?

[shg]

The formula in my last post gives exactly that result.

[mbwd]

So it does! I of course forgot to use the "ctrl+shift+enter" when I replied!

Thanks so much! And for sticking with me!


__________________________________
Question: is there a way to copy/paste that formula down the column? I am getting an error: "You cannot change part of an array."

NEVERMIND (I had to move to the next cell after the copied one first!)