+ Reply to Thread
Results 1 to 12 of 12

Ranking based on 2 criteria with named range

  1. #1
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    337

    Ranking based on 2 criteria with named range

    I am attempting to set up a ranking, however I have run into a slight problem. Not all staff will be included in the ranking. Obviously anyone without sales volume will be excluded (I have an If statement for this). In addition, only staff associated with certain location numbers (location is column A) will be ranked. I have these locations (there are 4 of them) set up in a named range (Tier_1). The named range is listed in column N. My formula is currently:

    =IF($C2="","-",RANK($C2,$C$2:$C$26,0)+COUNTIF($C$2:$C2,$C2)-1)

    This formula works, however it includes ALL staff. Column A in the attached example shows the location number. Only 4 locations qualify for a bonus, based on ranking, which is why I need to exclude all staff. I have played around with the formula a bit, but continue getting errors.
    ~*~ Sherry ~*~
    Poinciana, FL

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Ranking based on 2 criteria with named range

    Hi Sherry,

    You can use a pivot in this situation where you can select your location(s) in the page field and apply your existing formula on the right side of pivot, see the attachment. thanks.

    Book2(5).xlsx

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    337

    Re: Ranking based on 2 criteria with named range

    DILIPandey---

    I appreciate the information. However this report will be used by someone other than myself, and there will be 2 seperate location variables, one of which I mentioned in this post (the 4 location numbers in my named range). I need to find a way to incorporate the location numbers into the actual formula. Any other ideas?

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Ranking based on 2 criteria with named range

    Hi Sherry,


    I need to find a way to incorporate the location numbers into the actual formula.
    yes.. location number are already referenced into the formula next to pivot.. do you want to used named range into the formula here or what?
    Also, you can check / uncheck your location from the pivot filed... I still believe that pivot is good option. thanks.


    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  5. #5
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    337

    Re: Ranking based on 2 criteria with named range

    DILIPandey, honestly the users that will be doing this report don't know what pivot tables are. I need to have this as ONLY a formula. A pivot table is just not a reality for the end user.

  6. #6
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    337

    Re: Ranking based on 2 criteria with named range

    Okay, I am still working on this, and I made SOME progress. I have incorporated another comulmn to indicate which tier the location is associated with (Column D). I have highlighted the original ranking I set up (Column C), and I plan on deleting this column. Columns E & F represent the 2 tiers (Tier 1 and Tier 2). As you can see, the ranking #s in columns E & F correspond identially to the rankings listed in Column C... however the numbers are NOT consecutive. Tier 1 ranking does not have a 2 or 3, etc... and tier 2 does not have a 1, 4, or 5, etc... I have highlighted the 2 criteria for my ranking (in green), which are columns c & d. any help would be greatly appreciated.

  7. #7
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    337

    Re: Ranking based on 2 criteria with named range

    Well, I refused to be beaten. I spent of all of last night at work, and I was working on it for the last 2 hours. I was able to make this work finally! i thought I would share my results in case some one else can benefit from my refusal to give up! Thanks!

    =IF(D2=$O$1,SUMPRODUCT((D$2:D$26=$O$1)*(C$2:C$26>C2)/COUNTIFS(D$2:D$26,D$2:D$26&"",C$2:C$26,C$2:C$26&""))+1,"")

  8. #8
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    337

    Re: Ranking based on 2 criteria with named range

    This was "solved", however, I have just been advised that I must consider an additional crieteria in the event of a tie. Let';s say the additional component is in column E. This component is the VPG and is represented by a dollar amount. This column is only used in the event of a tie in ranking, so if there is not a tie, it is not a criteria. How would I integrate that into my existing formula, which I have already confirmed works perfectly??? Thanks in advance for all your help, it is greatly appreciated! Attached is a revision of my sample
    Last edited by Webbers; 04-18-2012 at 01:48 PM. Reason: Added sample spreadsheet

  9. #9
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Ranking based on 2 criteria with named range

    In your example you don't have any ties in column D, except where the column D and E values are blank, what should happen in that case, do you want the ranks to be different than those shown?

    Say you have only 5 Tier 1 values and column C is respectively 8000, 6000, 6000, 6000 and 4000 then under your current formula those are ranked as follows

    1,2,2,2,3

    So now if you split those by using the column E value, assuming column E is 100, 60, 50, 40 and 30 then presumably those ranks will change to

    1,2,3,4,5

    Is that right? What would happen if there were ties in column E too, e.g. the 50 becomes a 40, do you now have these ranks?

    1,2,3,3,4
    Last edited by daddylonglegs; 04-18-2012 at 02:03 PM.
    Audere est facere

  10. #10
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    337

    Re: Ranking based on 2 criteria with named range

    DaddyLongLegs,
    First of all, everyone "should" have a tier associated with them, however that may not be the case. If someone does not have a tier, they should not be ranked. There will be 1 column to rank the Tier 1 people and another column to rank the Tier 2 people. There should be no "missing" numbers within the sequence as only the relevant tiered people will actually receive a ranking. If for some reason there is a tie on the volumne (primary ranking aspect - Column C), then the VPG in column E will be used to break the tie. In this circumstance, it is unlikely that BOTH will be tied items. If the Volume is not tied, the VPG is not a consideration at all. Also, I want to be able to include the "tie breaker" factor into the existing formula somehow. I do not want an additional column, as I having multiple rankings to set up based on other criteria. Those other rankings will be set up the same way. I just don't want to have any extra columns within the spreadsheet. the one that was being used before (and everything was done manually)... had like 200 columns, most of which were useless and hidden! I want to have this ranking as an all in one. So if 8 people are listed as Tier 1, there will be rankings from 1 - 8, with 1 being the largest volume (and largest VPG if tie is a factor).

    And I follow your example, and that would be correct in the scenerio you described.

  11. #11
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Ranking based on 2 criteria with named range

    Quote Originally Posted by Webbers View Post
    ....In this circumstance, it is unlikely that BOTH will be tied items....
    In that case try this version

    =IF(D2=$O$1,COUNTIFS(D$2:D$26,O$1,C$2:C$26,">"&C2)+COUNTIFS(D$2:D$26,O$1,C$2:C$26,C2,E$2:E$26,">"&E2)+1,"")

  12. #12
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    337

    Re: Ranking based on 2 criteria with named range

    DaddyLongLegs-----

    PERFECT!!!! Thanks so much! Y a know I was so proud of myself when I initially solved this one on my own... before I was advised about the "tierbreaker" contigency! I then thought my head was gonna explode!! Thanks so much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1