+ Reply to Thread
Results 1 to 5 of 5

Compute rank within each divison

  1. #1
    Forum Contributor
    Join Date
    11-30-2012
    Location
    Seattle, WA
    MS-Off Ver
    Office 2007
    Posts
    212

    Compute rank within each divison

    Attached is a sample of the output I get from the program that maintains wins and losses for league teams. A - H are actual output in the format I get it in. I've added column I to tell me when the division changes and column K to tell me if there is a tie within a division. What I would like is a formula to create the rank for each team in it's division (or to mark it tied). I've manually done this in column I. I thought that RANK would do it, but different divisions have different numbers of teams so I would have to manually (I think) create the range for each and that's just as much work. Is there an easy way to get excel to do it for me?
    Attached Files Attached Files
    "Laugh? I thought I'd die!"

    Jimbo?

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Compute rank within each divison

    a semi-automated approach is in there.

    sort the data (by c in ascending order and then by h in descending order)

    columns I,J,K have formulas to yield what you need..
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    11-30-2012
    Location
    Seattle, WA
    MS-Off Ver
    Office 2007
    Posts
    212

    Re: Compute rank within each divison

    I am thinking you meant column B rather than C for the first sort. It looks like it will do the job perfectly. Thank you!!

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,740

    Re: Compute rank within each divison

    I'm not sure why you show so many ranks with a value of 3 in column J.

    If you want a RANK formula that adjusts to the division in column B, then you can use this formula in J2:

    =RANK(H2,INDEX($H$2:$H$77,MATCH(B2,$B$2:$B$77,0)):INDEX($H$2:$H$77,MATCH(B2,$B$2:$B$77,0)+COUNTIF(B:B,B2)-1))

    then copy down. You can see in J67 and J68 that the same rank has been allocated to successive teams, so your formula in column K could look at column J or column H.

    Hope this helps.

    Pete

  5. #5
    Forum Contributor
    Join Date
    11-30-2012
    Location
    Seattle, WA
    MS-Off Ver
    Office 2007
    Posts
    212

    Re: Compute rank within each divison

    This is the first step in determining seeding for a tournament. We break the teams into three categories: First place, Second place, and all others. Then each catagory gets sorted by win percentage to determine seeding. rcm's solution works great.

+ 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] Rank - Maintain Series if duplicate rank found
    By ascool_asice in forum Excel General
    Replies: 2
    Last Post: 10-11-2014, 12:35 PM
  2. Replies: 8
    Last Post: 02-20-2014, 07:03 PM
  3. Replies: 6
    Last Post: 11-30-2013, 09:14 AM
  4. Replies: 1
    Last Post: 11-30-2013, 06:48 AM
  5. symbol for divison problems; divison formula
    By excel general question ... help please in forum Excel General
    Replies: 4
    Last Post: 07-15-2006, 12:30 PM
  6. Replies: 1
    Last Post: 08-15-2005, 05:05 PM
  7. Divison by 0
    By jacob in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-26-2005, 04:06 PM

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