+ Reply to Thread
Results 1 to 12 of 12

Automatic ranking with three criteria

  1. #1
    Registered User
    Join Date
    02-01-2010
    Location
    Denmark
    MS-Off Ver
    Excel 2003
    Posts
    3

    Automatic ranking with three criteria

    I have sheet with names in the first column and then three colums with points. I need to rank the names after column 4. In case two names have the same amount of points in column 4, then the one with the highest number of points in column 2 should have the highest rank. If they have the same amount of points in column 2 as well, then column 3 decides who is ranked highest.

    It has to be automatic, so I only need to insert my data in another sheet. Then Excel will calculate the amount of points and then it should rank after these points. My sheet can calucate points but I can only get Excel to rank with one column as criteria. How can I solve this?

    My sheet looks like this (made up example):

    Name Column 2 Column 3 Column 4
    Emil 10 15 20
    Alex 5 14 30
    Frank 11 10 20

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Automatic ranking with three criteria

    Hi Tenshu,

    welcome to the forum.

    This is not an easy task. Have a look at this: http://www.cpearson.com/excel/Rank.aspx

    You will find a tie breaker formula but only for two levels. I'm not sure it's possible to do three levels at all.

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Automatic ranking with three criteria

    Here's a sheet where I show multi-column tie-breaking.

    Rank-TieBreaking.xls

    I took up to 6 levels of tie-breaking values.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Automatic ranking with three criteria

    An alternative for the formula in I2 and down is

    =SUMPRODUCT(B2:H2 / 100^COLUMN(B2:H2))
    Last edited by shg; 02-01-2010 at 09:06 PM.
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Registered User
    Join Date
    02-01-2010
    Location
    Denmark
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Automatic ranking with three criteria

    Thank you for your quick answers!

    I solved the problem with JBeaucaire sheet.

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Automatic ranking with three criteria

    You might want to extend his formula to include col H as the sixth tie breaker.

  7. #7
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Automatic ranking with three criteria

    Doh! Thanks SHG, I thought I'd already gone all the way out 6 tie-breakers, my bad.

    I've updated the sheet and added some more notes about usage.

    Rank-TieBreaking.xls

    =========
    If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


    (Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)

  8. #8
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Automatic ranking with three criteria

    JB, could you possibly attach the file with the forum's attachment facilities? We generally discourage file storage on external sites.

  9. #9
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Automatic ranking with three criteria

    Actually, we point OPs to external sources of code, concepts and samples files all the time. (MOREFUNC, cpearson, OzGrid, Aaron Blood, the sources are lengthy)

    Also, the external source in this instance is my own folder of help files, the site is Google-hosted, so it won't be going anywhere anytime in the years to come, hehe. I have seen some links to really some really fly-by-night site, so I agree it's worth watching!

    I do upload file that are custom solutions, but common unchanging files get the same treatment as any other resource that might get updated. This way, all the other threads where I used this link (a few so far) all get the updated versions.
    Last edited by JBeaucaire; 02-01-2010 at 10:49 PM.

  10. #10
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Automatic ranking with three criteria

    Links to the aforementioned sites and others normally go to a web page, not straight to an Excel file. I just want to give users the informed choice what they are opening and some information on the file size. With a direct link like that, they don't have that information.

  11. #11
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Automatic ranking with three criteria

    Quote Originally Posted by teylyn View Post
    Links to the aforementioned sites and others normally go to a web page, not straight to an Excel file. I just want to give users the informed choice what they are opening and some information on the file size. With a direct link like that, they don't have that information.
    Like this then?

    Jerry Beaucaire's Excel Tools
    (the file you want is Rank-TieBreaking.xls)


    Or this:

    Rank-TieBreaking.xls
    Last edited by JBeaucaire; 02-02-2010 at 12:14 AM.

  12. #12
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Automatic ranking with three criteria

    Yup, that's fine. Good luck with your web site!

+ 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