FrenchCelt
Board Regular
- Joined
- May 22, 2018
- Messages
- 214
- Office Version
- 365
- Platform
- Windows
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.
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.