Macro for Running a Series of Formulas for Multiple Categories

[FrenchCelt]

Hello,

I have a report that breaks down various scores for a number of different activities by employee. I want to run some statistical analysis per activity by employee to look for and delete outliers. I need the code to first run the average for each activity, then STDEV.P, and then finally assign a z-score to each individual employee for that activity, and then delete any z-scores above 3 or lower than -3. Here is how everything is broken down:

Column A has the list of all employees in the report.
Column C has a list of all activities sorted in alphabetical order.
Column F has the score I'm tracking.
Column G is for z-scores.
Column H is for average scores.
Column I is for standard deviations.


This is a rough idea of how it looks:

Employee|Hire Date|Activity|Quantity|Time|MS%|Z-Score|Average|Standard Deviation
user1.....|..............|CUT.....|1064.....|564.|157..|
user2.....|..............|CUT.....|587.......|286|170..|
user3.....|..............|CUT.....|1085.....|791|114..|
user1.....|..............|PICK...|365.......|254|91....|
user2.....|..............|PICK...|621.......|401|145..|
user3.....|..............|PICK...|299.......|224|82....|

The macro should first run the average of each MS% for all the people that have the activity CUT, then the STDEV.P, with each of those results being at H2 and I2 respectively. To obtain the z-score for each person with CUT, the formula is the MS% - average/STDEV.P. So the z-score for user1 in activity CUT would be =(F2-$H$2)/$I$2 and then I would want the z-score for every other person doing that activity, and then do it again for PICK (where, in this example, the average and standard deviation would be in H5 and I5), and on down for all activities. The code will need to accommodate a dynamic range for all variables, so I can't hard code static locations for these data points except knowing that the first set will always appear in G2, H2, and I2.

Once this part is complete, then I need the macro to run down the list of z-scores and simply delete the MS% score from any person with a z-score higher than 3 or lower than -3. After that I have all the code I need to run the rest of the data analysis.

Any help would be appreciated.

 

[FrenchCelt]

I made some changes to my report formatting by adding a table of all average scores and standard deviations for each activity to create a static location for each value on every report. The only thing I need the code to do is assign a z-score for each employee per activity.

Referring to the example I used above, the macro should always utilize $I$111 for average and $J$111 for standard deviation because CUT average is always in I111 and standard deviation is always in J111. The only dynamic range is found in the number of employees per activity.

So for user1 doing CUT, the z-score would be =(F2-$I$111)/$J$111, user2 would be =(F3-$I$111)/$J$111, and user3 would be =(F4-$I$111)/$J$111. What code would I use to make sure I assigned a z-score for all employees per activity for all activities (there are several)? The z-scores will still be in Column F.

 

[FrenchCelt]

Never mind, I was able to figure all this out on my own.