#### vegasbaby207

##### New Member

- Joined
- Nov 13, 2008

- Messages
- 44

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.