Formula to indicate bottom percentage of sales performers based on their results percentages? Number of reps change month to month?

[grayjeans]

Hey guys. This is my first post on these forums. Never felt the need to ask a question on here but I am completely stuck on how to do this and really need help.

I work for a sales organization and I am working on creating a report that will show which of our sales reps have been in the bottom 50% of performers for an entire quarter. Here is an example of what I am doing and what I need to do:

Rep	Month 1 Goal	Month 1 Actual	Performance		Rep	Month 2 Goal	Month 2 Actual	Performance		Rep	Month 3 Goal	Month 3 Actual	Performance
John	15000	14500	97%		John	15000	14500	97%		John	15000	13000	87%
Jack	15000	12900	86%		Jack	15000	12900	86%		Jack	15000	12500	83%
Jane	15000	17700	118%		Jane	0	17700	-		Jane	15000	18000	120%
Peter	0	1200	-		Peter	0	12400	-		Peter	15000	1200	8%
Paul	0	600	-		Paul	10000	11500	115%		Paul	15000	15000	100%
Mike	15000	12000	80%		Mike	15000	9500	63%		Mike	15000	12000	80%
Don	20000	24500	123%		Don	0	24500	-		Don	20000	25000	125%
Aaron	20000	29000	145%		Aaron	0	29000	-		Aaron	20000	29000	145%
Terry	20000	17000	85%		Terry	20000	17000	85%		Terry	20000	24500	123%
Lauren	20000	16700	84%		Lauren	20000	15000	75%		Lauren	20000	14000	70%

<tbody>
</tbody>

As you can see, some reps are on a quota plan for some months, and some are not. So when I say I need the bottom 50% of performers indicated, the number of performers may vary month to month. There are 10 total reps, but if they are not given a "goal" for a particular month, then they are not on a quota plan, and thus do not count when calculating this.

Those in bold are those who are in the bottom half for a particular month (i.e., Month 1: 8 reps on plan--bottom 50% would be bottom 4 reps; Month 2: 6 on plan--bottom would be 3; Month 3: all 10 reps on plan--bottom half is bottom 5 reps). Whether they are on the bottom or not is indicated in the Performance column in each table.

The reps that are in RED are those that have been in the bottom 50% for the entire quarter--these are the reps I need to indicate with a function or something.

How should I go about doing this? In order for a rep to be marked, they have to meet the criteria for ALL THREE MONTHS (bottom half of performers). The criteria never changes (which is bottom performer = bottom 50%) but the number of reps on plan WILL change as I stated above. Therefore, I do not know where to begin.

 

[Eric W]

Welcome to the forum.

How about:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Rep	Month 1 Goal	Month 1 Actual	Performance		Rep	Month 2 Goal	Month 2 Actual	Performance		Rep	Month 3 Goal	Month 3 Actual	Performance
2	John	15000	14500	97%		John	15000	14500	97%		John	15000	13000	87%
3	Jack	15000	12900	86%		Jack	15000	12900	86%		Jack	15000	12500	83%
4	Jane	15000	17700	118%		Jane	0	17700	-		Jane	15000	18000	120%
5	Peter	0	1200	-		Peter	0	12400	-		Peter	15000	1200	8%
6	Paul	0	600	-		Paul	10000	11500	115%		Paul	15000	15000	100%
7	Mike	15000	12000	80%		Mike	15000	9500	63%		Mike	15000	12000	80%
8	Don	20000	24500	123%		Don	0	24500	-		Don	20000	25000	125%
9	Aaron	20000	29000	145%		Aaron	0	29000	-		Aaron	20000	29000	145%
10	Terry	20000	17000	85%		Terry	20000	17000	85%		Terry	20000	24500	123%
11	Lauren	20000	16700	84%		Lauren	20000	15000	75%		Lauren	20000	14000	70%
12
13
14	Bottom half performers
15	Mike
16	Lauren

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

Sheet1​

Array Formulas Cell Formula A15 {=IFERROR(INDEX($A$2:$A$11,AGGREGATE(15,3,IF( (RANK($D$2:$D$11,$D$2:$D$11,0)>COUNTIF($D:$D,">0")/2)+ (RANK($I$2:$I$11,$I$2:$I$11,0)>COUNTIF($I:$I,">0")/2)+ (RANK($N$2:$N$11,$N$2:$N$11,0)>COUNTIF($N:$N,">0")/2)=3, ROW($D$2:$D$11)-ROW($D$2)+1),ROWS($A$15:$A15))),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>