Exclude some records from a SUMPRODUCT ranking formula

vegasbaby207

New Member
Joined
Nov 13, 2008
Messages
49
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.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Crystalyzer

Board Regular
Joined
Oct 18, 2011
Messages
177
Could you adjust your IFERROR formula to insert 999,999.00 instead of zero and then adjust the SUMPRODUCT formulas to ignore 999,999.00 as follows:

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

I haven't validated this with any data just trying to think it through.
 

vegasbaby207

New Member
Joined
Nov 13, 2008
Messages
49
It's giving me a #VALUE ! result in the Ranking formulas. Note that I had to replace the <> with < because of the addition of the small random number to the IFERROR result. But that shouldn't matter, should it?
 

Crystalyzer

Board Regular
Joined
Oct 18, 2011
Messages
177
I was able to get it to work with this formula and some test data.

=SUMPRODUCT((A1:A6="Red")*(B1:B6<>999999)*(B1:B6))+1

Result = 1.25


AB
1Blue.4
2Blue.3
3Red999999
4Red.25
5Green.3
6Green.2

<tbody>
</tbody>
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,462
Office Version
365
Platform
Windows
For your RankingsDESC try

=IF([@Achieved]<0.00001,"",SUMPRODUCT(--([RepGroup]=[@RepGroup]),--([@Achieved]>[Achieved]))+1-COUNTIFS([RepGroup],[@RepGroup],[Achieved],"<0.00001"))

.. or if you want/need to stick to SUMPRODUCT for it all, try

=IF([@Achieved]<0.00001,"",SUMPRODUCT(--([RepGroup]=[@RepGroup]),--([@Achieved]>[Achieved]))+1-SUMPRODUCT(--([RepGroup]=[@RepGroup]),--([Achieved]<0.00001)))
 
Last edited:

vegasbaby207

New Member
Joined
Nov 13, 2008
Messages
49
Hi,

Thank you both for helping. Peter, I haven't tested yours as I was able to adapt Crystalyzer's post to suit my requirements.

I did the following:
1. added a new Achieved column to my table (Achieved2), using the 999999 method as suggested
2. added a new column called NoNewRankingDESC which used the identical formula to the RankingDESC field, except that it used Achieved2 instead of Achieved. I did not have to add the [Achieved]<>999999 parameter.

I'm happy to share a sample of my table with the new columns in case it is useful to someone, but i am not able to attach files. Let me know if you'd like it and I can share it some other way.

Thanks again.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,462
Office Version
365
Platform
Windows
Peter, I haven't tested yours as I was able to adapt Crystalyzer's post to suit my requirements.
You are of course welcome to use whatever method you want, but why don't you give it a go anyway in a copy of your workbook since it doesn't require the extra column, just an adjustment to your current formula?
 
Last edited:

vegasbaby207

New Member
Joined
Nov 13, 2008
Messages
49
Hi Peter,

I gave it a go, but it unfortunately doesn't work in cases where OrdersThisPeriod is 0 or under. So, if a sales rep has a target of $1000, but have achieved no sales (quite possible), or even -ve sales (also possible, especially early on in the season when customers return stock that they were over-sold the previous season, but that's a story for another day!)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,462
Office Version
365
Platform
Windows
Hi Peter,

I gave it a go, but it unfortunately doesn't work in cases where OrdersThisPeriod is 0 or under.
We hadn't heard of that possibility before, but in any case, to help my understanding, I would be interested to know if are any of these RankingDESC incorrect and why? For example, should any/all of rows 7, 9 & 10 have a value rather than blank?

I have used your formula for Achieved and RankingsASC and mine for RankingsDESC.

Excel Workbook
ABCDEF
1RepGroupAchievedTargetThisPeriodOrdersThisPeriodRankingsASCRankingsDESC
2A1.3333413426812
3A0.8571505127621
4B3.02314E-06054
5A8.53403E-07035
6B0.50000077210531
7A-0.9999936665-56
8B1.078949663384113
9A2.1619E-06004
10A9.46406E-06503
11B0.900009305201822
Rank Excluding Some
 

vegasbaby207

New Member
Joined
Nov 13, 2008
Messages
49
Hi Peter,
Based on your data, the following rows should have a value in RankingDESC (I've indicated the value in brackets):

Row10 (1)
This in turn will lead to ranking changes for Row3 (2) and Row2 (3)

To translate this into Business Logic, the salesperson represented by row 10 had a Sales Target of $5, and has achieved actual sales of $0. He is clearly a worse performing rep than the one in Row 3 who had a target of $7 and achieved $6.
 

Forum statistics

Threads
1,089,421
Messages
5,408,133
Members
403,186
Latest member
123hpeinstall

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top