Exclude some records from a SUMPRODUCT ranking formula

[vegasbaby207]

Hi,


I have a table of SalesReps with a two ranking formulas. The resulting rankings are used on dashboards on other worksheets. The formulas rank the performance of SalesReps within each RepGroup in ASC and DESC order (so that we can report on the top performing and bottom performing reps). The ranking is based on what percentage of their sales target they have achieved.


The problem occurs for new sales reps who don't have targets. Their "Achieved" value (Orders / Target) is undefined, corrected by formula to 0%. Because their achieved is 0%, they always top the bottom-performing sales reps, which is untrue for our purposes. We'd like to suppress the ranking of all reps with no target from the bottom performing ranking (or rank them last).


Here are the formulas I am using:


OrdersThisPeriod : SUMIFS formula referencing an external database
TargetThisPeriod : SUMIFS formula referencing an external database
Achieved : =IFERROR([@OrdersThisPeriod]/[@TargetThisPeriod],0)+(RAND()/100000) <---- I've added the random number to the formula so that I am almost guaranteed a unique Achieved percentage, which keeps my Rankings clean and unique.


RankingASC : =SUMPRODUCT(--([RepGroup]=[@RepGroup]),--([@Achieved]<[Achieved]))+1
RankingDESC : =SUMPRODUCT(--([RepGroup]=[@RepGroup]),--([@Achieved]>[Achieved]))+1


If required, I can upload a sheet with the table, but I would need to anonymise the data first which would take some time.


Thanks in advance.

 

[Peter_SSs]

OK, thanks. What about row 7 then?
That target is also 5 (like row 10) but orders are -5. Doesn't that make that row rank even worse than row 10?

 

[vegasbaby207]

Yes, correct, sorry I missed that one.

 

[Peter_SSs]

Yes, correct, sorry I missed that one.

OK, how does this go then?

Excel Workbook
	A	B	C	D	E	F
1	RepGroup	Achieved	TargetThisPeriod	OrdersThisPeriod	RankingsASC	RankingsDESC
2	A	1.333336925	6	8	1	4
3	A	0.857147079	7	6	2	3
4	B	7.35004E-06	0	5	4
5	A	8.80655E-06	0	3	4
6	B	0.500009816	10	5	3	1
7	A	-0.999992607	5	-5	6	1
8	B	1.078955195	38	41	1	3
9	A	9.63551E-06	0	0	3
10	A	8.33662E-06	5	0	5	2
11	B	0.900009932	20	18	2	2
Rank Excluding Some (2)