Similar to RANKIF? How to Rank a list of people based on multiple criteria in the same array?

[grayjeans]

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:

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.

 

[Eric W]

	A	B	C	D	E	F	G	H	I
1		Sales Rep	Level	Month 1 Quota	Month 1 Sales	Month 1 Performance	Month 2 Quota	Month 2 Sales	Month 2 Performance
2	OK	Adam	Veteran	X	24,000	96%	25,000	18,000	72%
3	OK	Bart	Veteran	X	23,000	92%	25,000	16,000	64%
4	OK	James	Veteran	25,000	20,000	80%	25,000	22,500	90%
5	OK	Peter	Veteran	25,000	22,000	88%	25,000	25,000	100%
6	OK	Andrew	Senior	15,000	14,000	93%	15,000	10,250	68%
7	OK	John	Senior	15,000	15,000	100%	15,000	11,400	76%
8	OK	Jude	Senior	15,000	11,000	73%	15,000	12,200	81%
9	OK	Nathan	Senior	15,000	13,000	87%	15,000	14,700	98%
10	OK	Matthew	Junior	X	6,250	83%	7,500	10,000	133%
11	OK	Philip	Junior	X	5,250	70%	7,500	7,100	95%
12	Improve	Simon	Junior	7,500	5,000	67%	7,500	2,500	33%
13	Improve	Thomas	Junior	7,500	4,500	60%	7,500	1,000	13%
14

<tbody>
</tbody>

Sheet16​

Worksheet Formulas Cell Formula A2 =IF(B2="","",IF(AND(AND(SUMPRODUCT(--((--ISNUMBER($D$2:$D$20)*$F$2:$F$20)>(--ISNUMBER(D2)*F2)))+1>SUMPRODUCT(--ISNUMBER($D$2:$D$20))/2,ISNUMBER(D2)),AND(SUMPRODUCT(--((--ISNUMBER($G$2:$G$20)*$I$2:$I$20)>(--ISNUMBER(G2)*I2)))+1>SUMPRODUCT(--ISNUMBER($G$2:$G$20))/2,ISNUMBER(G2))),"Improve","OK")) <tbody> </tbody>

<tbody>
</tbody>

Here's one option. Fairly long, I might see if it can be shortened.

Based on your explanation, I believe James and Simon should be included in the first month, and Peter and Nathan should not. If that's not so, let me know. I created this CF formula to automatically bold those in the bottom half:

=AND(SUMPRODUCT(--((--ISNUMBER(D$2:D$20)*F$2:F$20)>(--ISNUMBER(D1)*F1)))+1>SUMPRODUCT(--ISNUMBER(D$2:D$20))/2,ISNUMBER(D1))


Also, did the answer I gave on your other question work? Did you try it?

 

[grayjeans]

Yes, sorry about the confusion. Peter should NOT be included in the first month, but James should. Because they're are only 2 Veterans on quota for that month. So half of 2 is 1, and the bottom 1 rep is Peter. My mistake.

However, Simon should NOT be included (because he has a performance rate of 67%. Thomas being included is correct because he has the lowest percentage in the group of junior reps (remember there are only 2 reps on quota plan in that group for the first month; so just looking for the bottom 1 rep).

Nathan should be included as well so that is also correct. Because for the first month, all 4 senior reps are on quota. That means I'm looking for the bottom 2 (2/4=50%). So he and Jude, both seniors, are in the bottom 50% of their group.

hopefully that makes sense. Thanks for the reply. I will test out the formula you provided.

 

[Eric W]

The formula as written doesn't take into account the Level. I'll need to adjust the formula. I'll reply back when I have something.

 

[Eric W]

Try this:

	A	B	C	D	E	F	G	H	I
1		Sales Rep	Level	Month 1 Quota	Month 1 Sales	Month 1 Performance	Month 2 Quota	Month 2 Sales	Month 2 Performance
2	OK	Adam	Veteran	X	24,000	96%	25,000	18,000	72%
3	OK	Bart	Veteran	X	23,000	92%	25,000	16,000	64%
4	OK	James	Veteran	25,000	20,000	80%	25,000	22,500	90%
5	OK	Peter	Veteran	25,000	22,000	88%	25,000	25,000	100%
6	OK	Andrew	Senior	15,000	14,000	93%	15,000	10,250	68%
7	OK	John	Senior	15,000	15,000	100%	15,000	11,400	76%
8	OK	Jude	Senior	15,000	11,000	73%	15,000	12,200	81%
9	OK	Nathan	Senior	15,000	13,000	87%	15,000	14,700	98%
10	OK	Matthew	Junior	X	6,250	83%	7,500	10,000	133%
11	OK	Philip	Junior	X	5,250	70%	7,500	7,100	95%
12	OK	Simon	Junior	7,500	5,000	67%	7,500	2,500	33%
13	Improve	Thomas	Junior	7,500	4,500	60%	7,500	1,000	13%
14

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula A2 =IF(B2="","",IF(IF(ISNUMBER(D2),IF(SUMPRODUCT(--(--ISNUMBER($D$2:$D$20)*--($C$2:$C$20=C2)*$F$2:$F$20>=(--ISNUMBER(D2)*F2)))>SUMPRODUCT(--($C$2:$C$20=C2),--ISNUMBER($D$2:$D$20))/2,1,0),0)+IF(ISNUMBER(G2),IF(SUMPRODUCT(--(--ISNUMBER($G$2:$G$20)*--($C$2:$C$20=C2)*$I$2:$I$20>=(--ISNUMBER(G2)*I2)))>SUMPRODUCT(--($C$2:$C$20=C2),--ISNUMBER($G$2:$G$20))/2,1,0),0)=2,"Improve","OK")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>