+ Reply to Thread
Results 1 to 2 of 2

ranking a range based on the average value

  1. #1
    Registered User
    Join Date
    03-02-2013
    Location
    iran
    MS-Off Ver
    Excel 2010
    Posts
    1

    Lightbulb ranking a range based on the average value

    Hi everyone!

    I couldn't find an answer to my problem anywhere so i decided to ask, hope you can help.

    so I want assign a rank (from 1 to 10) to a range of data. so the biggest number receives rank 1 and the smallest number recieves 10. all the numbers in between should recieve a relative number from 2 to 9.

    No. range A rank
    1 254 8
    2 546 6
    3 231 8
    4 863 2
    5 346 7
    6 798 4
    7 45 10
    8 363 7
    9 142 9
    10 865 2
    11 765 4
    12 968 1
    13 544 6


    I entered the rank column by hand to show the kind of result i am looking for. assign a rank from 1 to 10 to range. as you can see, row 1 and row 3 have the same rank, becaese they are close and they can both be ranked 8. so we can have two numbers that are ranked 1, or 10. it's relative.

    I'm not sure if my explaination was confusing or not, let's see what you guys think.
    thanks a lot in advance.

  2. #2
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    Office 365
    Posts
    797

    Re: ranking a range based on the average value

    Try utilizing the built in Percent Rank formula
    With your data in B2 - B14... try this in C3.
    Note that I am rounding the value of the percent rank multiplied by 10 as the rank will be in decimal form. I am also subtracting that from 10 as your order is in the reverse order in which it generates originally.
    =ROUND(IF(10-(PERCENTRANK.INC($B$2:$B$14,$B$2:$B2,3)*10)<1,1,10-(PERCENTRANK.INC($B$2:$B$14,$B$2:$B2,3)*10)),0)

    Its either that or you Round down the number divided by 100 also taken out of 10 due to your order. =10-ROUNDDOWN(B2/100,0) pretty close to the same results as the percent rank, but this assumes the user is never over 999 in points
    -If you think you are done, Start over - ELeGault

+ 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. [SOLVED] Student ranking based on pass or fail and average grade (multiple condition)
    By zalya in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-08-2021, 12:37 AM
  2. How to Get the Average of a Range Based on 2 Conditions
    By justinprime in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-25-2013, 04:19 PM
  3. Replies: 8
    Last Post: 06-11-2012, 04:08 AM
  4. [SOLVED] Ranking based on 2 criteria with named range
    By Webbers in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-19-2012, 07:21 AM
  5. Ranking a revolving average?
    By Jeremiahm in forum Excel General
    Replies: 1
    Last Post: 12-01-2009, 03:36 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