Macro for Running a Series of Formulas for Multiple Categories

FrenchCelt

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

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top