elsenorjose
New Member
- Joined
- Feb 27, 2004
- Messages
- 1
I have a list of numbers (unit production per sales person) and I want to identify which sales people fall in the top 33%, the middle 33%, and the bottom 33%. Given this example:
Rep A - 10 units
Rep B - 10 units
Rep C - 15 units
Rep D - 18 units
Rep E - 20 units
Rep F - 23 unites
I can identify that Reps A and B are in the bottom 3rd, Reps C and D are in the middle 3rd, and Reps E and F are in the top 3rd.
Of course my list is not so simple. I have tried the rank and percentile tool in the analysis toolpak and it's not quite what I need. I have also created a formula which calculates the median average and then based on that average gives me values above or below the median but this doesn't quite work because given value X as my median, x-1 goes to the low range and x+1 goes into the high range but of course that wouldn't be true because in real life, one unit in either direction won't affect your score that much.
Has anyone solved a similar problem?
Rep A - 10 units
Rep B - 10 units
Rep C - 15 units
Rep D - 18 units
Rep E - 20 units
Rep F - 23 unites
I can identify that Reps A and B are in the bottom 3rd, Reps C and D are in the middle 3rd, and Reps E and F are in the top 3rd.
Of course my list is not so simple. I have tried the rank and percentile tool in the analysis toolpak and it's not quite what I need. I have also created a formula which calculates the median average and then based on that average gives me values above or below the median but this doesn't quite work because given value X as my median, x-1 goes to the low range and x+1 goes into the high range but of course that wouldn't be true because in real life, one unit in either direction won't affect your score that much.
Has anyone solved a similar problem?