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
1	Name	Value			STDEV
2	Jimmy	8		Jimmy	1.00
3	Jimmy	6		Chris	-
4	Chris	7		Bob	4.24
5	Bob	3
6	John	1
7	Bob	9
8	Jimmy	7

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

​