+ Reply to Thread
Results 1 to 3 of 3

Rank / Large function

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-21-2009
    Location
    England
    MS-Off Ver
    Excel 2016
    Posts
    710

    Rank / Large function

    Hi there, in column A i have numbers. Some of which are repeated.

    I want to be able to rank teh numbers without the repeated ones counting twice.

    For instance if my data in col a was

    4
    15
    7
    21
    6
    16
    15
    7
    8
    3
    4

    and I want to rank largest to smallest it currently shows

    21
    16
    15
    15
    8 etc etc

    How can i correct the formula so 15 only shows once?

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

    Re: Rank / Large function

    Try this to get a list in descending order (without duplicates). Assumes data in A2:A100, adjust as required

    In C2

    =MAX(A2:A100)

    in C3 copied down

    =IF(C2="","",IFERROR(LARGE(A$2:A$100,COUNTIF(A$2:A$100,">="&C2)+1),""))
    Audere est facere

  3. #3
    Forum Contributor
    Join Date
    04-21-2009
    Location
    England
    MS-Off Ver
    Excel 2016
    Posts
    710

    Re: Rank / Large function

    Great thanks

+ 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