Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Exclude some records from a SUMPRODUCT ranking formula

  1. #1
    New Member
    Join Date
    Nov 2008
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Exclude some records from a SUMPRODUCT ranking formula

    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.

  2. #2
    Board Regular
    Join Date
    Oct 2011
    Location
    Pompano Beach, FL
    Posts
    174
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Exclude some records from a SUMPRODUCT ranking formula

    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.
    Using Windows 10, Excel 365. Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel.
    To open the VB editor, press Alt + F11
    To run code from the Excel window, press Alt + F8, double click macro name

  3. #3
    New Member
    Join Date
    Nov 2008
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Exclude some records from a SUMPRODUCT ranking formula

    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?

  4. #4
    Board Regular
    Join Date
    Oct 2011
    Location
    Pompano Beach, FL
    Posts
    174
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Exclude some records from a SUMPRODUCT ranking formula

    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


    A B
    1 Blue .4
    2 Blue .3
    3 Red 999999
    4 Red .25
    5 Green .3
    6 Green .2
    Using Windows 10, Excel 365. Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel.
    To open the VB editor, press Alt + F11
    To run code from the Excel window, press Alt + F8, double click macro name

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,361
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Exclude some records from a SUMPRODUCT ranking formula

    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 by Peter_SSs; Oct 23rd, 2019 at 12:40 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  6. #6
    New Member
    Join Date
    Nov 2008
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Exclude some records from a SUMPRODUCT ranking formula

    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.

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,361
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Exclude some records from a SUMPRODUCT ranking formula

    Quote Originally Posted by vegasbaby207 View Post
    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 by Peter_SSs; Oct 23rd, 2019 at 01:37 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  8. #8
    New Member
    Join Date
    Nov 2008
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Exclude some records from a SUMPRODUCT ranking formula

    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!)

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,361
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Exclude some records from a SUMPRODUCT ranking formula

    Quote Originally Posted by vegasbaby207 View Post
    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.

    Rank Excluding Some

    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

    Spreadsheet Formulas
    CellFormula
    B2=IFERROR([@OrdersThisPeriod]/[@TargetThisPeriod],0)+(RAND()/100000)
    E2=SUMPRODUCT(--([RepGroup]=[@RepGroup]),--([@Achieved]<[Achieved]))+1
    F2=IF([@Achieved]<0.00001,"",SUMPRODUCT(--([RepGroup]=[@RepGroup]),--([@Achieved]>[Achieved]))+1-COUNTIFS([RepGroup],[@RepGroup],[Achieved],"<0.00001"))


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  10. #10
    New Member
    Join Date
    Nov 2008
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Exclude some records from a SUMPRODUCT ranking formula

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •