Results 1 to 4 of 4

Thread: Help with ranking

  1. #1
    Acolyte thousand's Avatar
    Join Date
    Apr 2017
    Posts
    24
    Articles
    0
    Excel Version
    2010

    Help with ranking



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Are there any ways to rank this to show all 65 as 1, all 7 as 2 and all 6 as 3 etc...?

    Thanks in advance
    65
    65
    65
    65
    7
    7
    7
    7
    7
    7
    7
    6
    6
    6
    6
    6
    6
    6
    6
    6
    6

  2. #2
    Acolyte Heyjoe's Avatar
    Join Date
    Jan 2019
    Location
    USA
    Posts
    48
    Articles
    0
    Excel Version
    2019
    I will assume that your numbers are in column A. I will also assume that your numbers are always sorted in descending order as you have them. Put a 1 in cell b1. Put the following formula in b2 and copy it down:
    IF(A2=A1,B1,B1+1).

    The rank.eq formula could be used if the numbers are not in descending order. However, using this formula all of the 65's would be ranked 1, all of the 7's would be ranked would be 5 and all of the 6's would be 12. The reason all of the 7's would be 5 is because the first 7 is the 5th number.
    '

  3. #3
    Acolyte Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    97
    Articles
    0
    Excel Version
    2013, 2016, O365
    Using your posted data in A2:A22
    This regular formula, copied down, ranks each value ignoring order, but without rank gaps:
    Code:
    B2: =IF(A2="","",SUMPRODUCT((A2<=A$2:A$30)/COUNTIF(A$2:A$30,A$2:A$30&"")))
    Is that something you can work with?
    Ron Coderre
    Former Microsoft MVP (2006-2015)

  4. #4
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    877
    Articles
    0
    Excel Version
    Excel 2013
    Another option, in B2
    Code:
    =IF(A2<>A1;MAX($B$1:B1)+1;B1)
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

Tags for this Thread

Posting Permissions

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