I have been driving myself insane all day trying to figure this out and I am at my wits end. So I need help
I know there is not a specific RANKIF or RANKIFS function, but I need something that does essentially that. I'll try my best to explain; it is a bit complicated. I have a group of sales reps, and there are four different categories that those sales reps fall into. I need to rank those reps by their performances, and indicate which of them are in the bottom 50% of performers in there group for two consecutive months. But that's where I am running into trouble. I have been able to use RANK so that I can indicate which reps were in the bottom 50% of the entire organization...but that's not what I need and I cannot figure out to indicate which were in the bottom 50% in JUST THEIR GROUPS.
Also, not all reps are figured into the equation. They are only counted IF they have a goal set for the month (i.e., they are on a quota plan). Here is an example:
<tbody>
</tbody>
An "X" indicates the person was not on a quota plan for the month, so they don't get counted. So instead of finding the bottom 2 people in the group of four, I'm just finding the bottom person (bottom 50%; because there's only 2 people in the group who are competing).
Reps in BOLD are those who are in the bottom 50% for the month. In order to meet the criteria, they must be below 50% for two consecutive months. Thomas is the only one who meets that criteria. I am using the performance columns (5 and 8) to indicate whether or not they are in the bottom 50%.
I will have another column before this table where the formula will go and if anyone meets BOTH criteria, an "IMPROVE" mark will appear; if they do not meet both criteria, it will say "OK." So if I make a new column A, what formula should go there to give me this result?
Also...the number of reps may vary month to month as time goes on. It will ALWAYS be the bottom 50%, just not a fixed NUMBER of reps in a particular group, if that is worth mentioning.
PLEASE HELP BEFORE I GO INSANE.
I know there is not a specific RANKIF or RANKIFS function, but I need something that does essentially that. I'll try my best to explain; it is a bit complicated. I have a group of sales reps, and there are four different categories that those sales reps fall into. I need to rank those reps by their performances, and indicate which of them are in the bottom 50% of performers in there group for two consecutive months. But that's where I am running into trouble. I have been able to use RANK so that I can indicate which reps were in the bottom 50% of the entire organization...but that's not what I need and I cannot figure out to indicate which were in the bottom 50% in JUST THEIR GROUPS.
Also, not all reps are figured into the equation. They are only counted IF they have a goal set for the month (i.e., they are on a quota plan). Here is an example:
Sales Rep | Level | Month 1 Quota | Month 1 Sales | Month 1 Performance | Month 2 Quota | Month 2 Sales | Month 2 Performance |
Adam | Veteran | X | 24,000 | 96% | 25,000 | 18,000 | 72% |
Bart | Veteran | X | 23,000 | 92% | 25,000 | 16,000 | 64% |
James | Veteran | 25,000 | 20,000 | 80% | 25,000 | 22,500 | 90% |
Peter | Veteran | 25,000 | 22,000 | 88% | 25,000 | 25,000 | 100% |
Andrew | Senior | 15,000 | 14,000 | 93% | 15,000 | 10,250 | 68% |
John | Senior | 15,000 | 15,000 | 100% | 15,000 | 11,400 | 76% |
Jude | Senior | 15,000 | 11,000 | 73% | 15,000 | 12,200 | 81% |
Nathan | Senior | 15,000 | 13,000 | 87% | 15,000 | 14,700 | 98% |
Matthew | Junior | X | 6,250 | 83% | 7,500 | 10,000 | 133% |
Philip | Junior | X | 5,250 | 70% | 7,500 | 7,100 | 95% |
Simon | Junior | 7,500 | 5,000 | 67% | 7,500 | 2,500 | 33% |
Thomas | Junior | 7,500 | 4,500 | 60% | 7,500 | 1,000 | 13% |
<tbody>
</tbody>
An "X" indicates the person was not on a quota plan for the month, so they don't get counted. So instead of finding the bottom 2 people in the group of four, I'm just finding the bottom person (bottom 50%; because there's only 2 people in the group who are competing).
Reps in BOLD are those who are in the bottom 50% for the month. In order to meet the criteria, they must be below 50% for two consecutive months. Thomas is the only one who meets that criteria. I am using the performance columns (5 and 8) to indicate whether or not they are in the bottom 50%.
I will have another column before this table where the formula will go and if anyone meets BOTH criteria, an "IMPROVE" mark will appear; if they do not meet both criteria, it will say "OK." So if I make a new column A, what formula should go there to give me this result?
Also...the number of reps may vary month to month as time goes on. It will ALWAYS be the bottom 50%, just not a fixed NUMBER of reps in a particular group, if that is worth mentioning.
PLEASE HELP BEFORE I GO INSANE.