+ Reply to Thread
Results 1 to 3 of 3

Excel Rank Duplicates then preferred rank

  1. #1
    Registered User
    Join Date
    03-07-2005
    Posts
    6

    Excel Rank Duplicates then preferred rank

    Hi All,
    I'm stumped.
    I have a list of values that I would like to rank. E.g Below

    Cell Value Preferred Rank
    A1 95 1
    A2 130 3
    A3 103 2
    A4 103 4
    A5 95 5

    What I would like to do is rank the lowest value which is 95 and compare it to a preferred rank listing. As there are two values of 95, I want A1 to show as rank 1 and A5 as rank 2 as it has a lower preferred rank.

    Another example:
    A1 140 1
    A2 130 3
    A3 103 2
    A4 103 4
    A5 95 5

    In this case A5 is the lowest value and there are no other duplicates, so this should be ranked as 1. However cell A3 and A4 are duplicates but based on the preferred rank they show as 2 and 4. Therefore the rank order should be A5 = 1, A3 = 2 and A4 = 3.

    Confusing as hell . PLease let me know if you need me to elaborate further. Not sure if this can be done, that's why i'm here posting.

    Please HELP
    Last edited by Economic; 04-05-2009 at 07:34 PM. Reason: formatting

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

    Re: Excel Rank Duplicates then preferred rank

    If the numbers to rank are in A1:A5 and "preferred ranks" are in B1:B5 then use this formula in C1 copied down

    =RANK(A1,A$1:A$5,1)+SUMPRODUCT((A$1:A$5=A1)*(B$1:B$5<B1))

  3. #3
    Registered User
    Join Date
    03-07-2005
    Posts
    6

    Re: Excel Rank Duplicates then preferred rank

    you my friend are a true genius.
    I was stumped for hours on this one almost coming to the conclusion it cannot be done. Now all I need to do is understand the formula you wrote for next time.

    Thanks again, really appreciated.

+ 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