Need help calculating St Deviation of Dynamic List

[GetHitFooL]

Good Afternoon Guys!

So here is my problem. I have a list of names on sheet 1, roughly 60 different people. Then on sheet two that contains all my data, I have 2000 rows which includes everyones numbers. In column D of sheet 2 I have the names and in column K I have the value.

I know that I could do =stdev() function and just go through the entire list on sheet3 to capture all the values but I want this to be a living document that is able to refresh using my macro.

I currently already have a macro that runs through all the data and then calculates the average so I have the loop.

For Example, lets say sheet 1 has the following info:

Column A
Name
Jimmy
John
Bob
Chris

Column B
St Deviation


On sheet 2
Name
Jimmy
Jimmy
Chris
Bob
John
Bob
Jimmy

Value
8
6
7
3
1
9
7


So for this macro I want it to loop through and compute the st dev of each. Jimmy for example would get the formula =stdev(8,6,7)

 

[StephenCrump]

Use a macro if you like to generate the unique name list, but all you need for the standard deviation is an Excel formula.

E2: {=IFERROR(STDEV.S(IF(A$2:A$8=D2,B$2:B$8)),"-")} Array-entered

You could use Excel or VBA to make the range dynamic.

	A	B	C	D	E
Name
Jimmy	Jimmy
Jimmy	Chris
Chris	Bob
Bob
John
Bob
Jimmy

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]Value[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]STDEV[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]1.00[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]-[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4.24[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

​