+ Reply to Thread
Results 1 to 4 of 4

RANK - Non unique values by subset.

  1. #1
    Registered User
    Join Date
    03-11-2013
    Location
    Charlotte
    MS-Off Ver
    Excel 2010
    Posts
    2

    RANK - Non unique values by subset.

    Hello,
    I need to rank the values of a specific column, but based on subsets of drivers as shown below. Also, I want to rank unique values so that when 2 values tie with the same rank, the next value only increments by 1 and not 2. Data may not be in any particular order.

    Driver Miles Rank
    Matt 20 2
    Matt 50 3
    Bob 50 2
    Matt 10 1
    Matt 10 1
    Bob 25 1


    If you were to sort this...
    Matt 10 1
    Matt 10 1
    Matt 20 2
    Matt 50 3

    Bob 25 1
    Bob 50 2

    The following formula works great for ranking the entire data set, but I need to somehow incorporate it to look at subsets (drivers) when ranking
    =SUM(--ISNUMBER(MATCH(ROW(INDIRECT("1:"&RANK(DataTable[@Miles],DataTable[Miles],1))),RANK(DataTable[Miles],DataTable[Miles]),0)))

    Any help is greatly appreciated!

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: RANK - Non unique values by subset.

    Try this...

    Data Range
    A
    B
    C
    1
    Driver
    Miles
    Rank
    2
    Matt
    20
    2
    3
    Matt
    50
    3
    4
    Bob
    50
    2
    5
    Matt
    10
    1
    6
    Matt
    10
    1
    7
    Bob
    25
    1

    This array formula** entered in C2 and copied down:

    =SUM(IF(FREQUENCY(IF(DataTable[Driver]=DataTable[@Driver],IF(DataTable[Miles]<DataTable[@Miles],DataTable[Miles])),DataTable[Miles]),1))+1

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    03-11-2013
    Location
    Charlotte
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: RANK - Non unique values by subset.

    Thanks Tony! This looks like it's working.

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: RANK - Non unique values by subset.

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. RANK - Non unique values, contiguous rank required
    By PaulBo in forum Excel General
    Replies: 9
    Last Post: 02-20-2014, 10:34 AM
  2. Formula to Rank and populate values base don Rank
    By Excel Dumbo in forum Excel General
    Replies: 3
    Last Post: 10-09-2012, 02:02 AM
  3. [SOLVED] Average of a subset of values within columns of various lengths
    By mrp16 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-31-2012, 10:37 PM
  4. [SOLVED] How to enter symbols for subset or element of a subset in Excel?
    By rwcita in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-23-2006, 05:30 PM
  5. Replies: 4
    Last Post: 12-01-2005, 12:00 PM

Tags for this Thread

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