Hi folks, I’m stumbling through a few excel exercises that I wish I was better at. I wondered if someone can help me with a few pointers. My first ever excel forum help request! If anyone reads on – thank you. My problem is this:
I have a spreadsheet of employee sales performance data, one employee per row. In columns I have various details for each employee, including sales generated in $, number of days worked, the type of accounts they work on (small, medium, or large) and the team they are in (red, blue, yellow, green or pink). What I am trying to do is to calculate the average sales per day worked for employees in different teams, working on different accounts. So for instance, for those employees working from the red team working on large accounts might generate $100 per day on average, while those on medium accounts in blue team may generate $250 per day. Once I have the average for each team/account segmentation I want to determine the 5% of employees that are furthest from the average for their specific team/account bracket (it doesn’t matter if they are anomalously higher or lower than the average, just that they are in the most anomalous 5%. I can then review these manually. Does that make sense?
I have used a pivot table to calculate the team/account bracket averages, so these figures are now in a table (accounts along top, teams down side). So now I want to insert a column in my data sheet that will, on a row by row basis:
I hope that makes some sense to someone. Thanks for reading if you got this far. Apologies if this is not the kind of question the forum is designed for.
I have a spreadsheet of employee sales performance data, one employee per row. In columns I have various details for each employee, including sales generated in $, number of days worked, the type of accounts they work on (small, medium, or large) and the team they are in (red, blue, yellow, green or pink). What I am trying to do is to calculate the average sales per day worked for employees in different teams, working on different accounts. So for instance, for those employees working from the red team working on large accounts might generate $100 per day on average, while those on medium accounts in blue team may generate $250 per day. Once I have the average for each team/account segmentation I want to determine the 5% of employees that are furthest from the average for their specific team/account bracket (it doesn’t matter if they are anomalously higher or lower than the average, just that they are in the most anomalous 5%. I can then review these manually. Does that make sense?
I have used a pivot table to calculate the team/account bracket averages, so these figures are now in a table (accounts along top, teams down side). So now I want to insert a column in my data sheet that will, on a row by row basis:
- Identify the account/team bracket the row relates to
- Take the sales per days worked figure for that row
- Look up the respective average sales/day for that specific account/team bracket from the table I have created
- Determine whether or not the value in that row is in the 95% of employees closest to the average for that account/team bracket (note that some account/brackets might have 100 employees and others might have 1,000)
I hope that makes some sense to someone. Thanks for reading if you got this far. Apologies if this is not the kind of question the forum is designed for.
