Exclude some records from a SUMPRODUCT ranking formula

vegasbaby207

Board Regular
Joined
Nov 13, 2008
Messages
53
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.
 
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?
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Yes, correct, sorry I missed that one.
OK, how does this go then?

Excel Workbook
ABCDEF
1RepGroupAchievedTargetThisPeriodOrdersThisPeriodRankingsASCRankingsDESC
2A1.3333369256814
3A0.8571470797623
4B7.35004E-06054
5A8.80655E-06034
6B0.50000981610531
7A-0.9999926075-561
8B1.078955195384113
9A9.63551E-06003
10A8.33662E-065052
11B0.900009932201822
Rank Excluding Some (2)
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top