Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 22

Thread: reverse ranking

  1. #1
    Join Date
    23rd July 2005
    Posts
    184

    reverse ranking

    I have a column of events,there are 7000 in my sheet.The scores for each contestant in the event are in col.2. In col 4 I have the rank printed for each contestant in each event in ascending order.I want to know how to print them in opposite order i.e highest in each event is ranked 1 etc into column 5.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    31st January 2003
    Location
    Portsmouth, UK
    Posts
    748

    Re: reverse ranking

    from Excel Help
    RANK
    See Also

    Returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list. (If you were to sort the list, the rank of the number would be its position.)

    Syntax

    RANK(number,ref,order)

    Number is the number whose rank you want to find.

    Ref is an array of, or a reference to, a list of numbers. Nonnumeric values in ref are ignored.

    Order is a number specifying how to rank number.

    If order is 0 (zero) or omitted, Microsoft Excel ranks number as if ref were a list sorted in descending order.


    If order is any nonzero value, Microsoft Excel ranks number as if ref were a list sorted in ascending order.

    Remarks

    RANK gives duplicate numbers the same rank. However, the presence of duplicate numbers affects the ranks of subsequent numbers. For example, in a list of integers, if the number 10 appears twice and has a rank of 5, then 11 would have a rank of 7 (no number would have a rank of 6).

    Examples

    If A1:A5 contain the numbers 7, 3.5, 3.5, 1, and 2, respectively, then:

    RANK(A2,A1:A5,1) equals 3
    RANK(A1,A1:A5,1) equals 5
    D

    Better a bad day on the water than a good day in the office

  3. #3
    Join Date
    31st January 2003
    Location
    Portsmouth, UK
    Posts
    748

    Re: reverse ranking

    Decided to have a quick play and see if i could get rid of the indirect for you...this works as long as the sheet is sorted by event number and replicates the uploaded file
    VB:
    =RANK($B2,OFFSET($B$1,MATCH($A2,$A$2:$A$26,0), 
    0,MATCH($A2,$A$2:$A$26,1),1),1) 
    
    
    and this does the same, but list the order the other way round
    VB:
    =RANK($B2,OFFSET($B$1,MATCH($A2,$A$2:$A$26,0),0,MATCH($A2,$A$2:$A$26,1),1),0) 
    
    
    D

    Better a bad day on the water than a good day in the office

  4. #4
    Join Date
    23rd July 2005
    Posts
    184

    Re: reverse ranking

    Thanks Dave,that works. There is only one problem,each event is descibed by
    3 columns,date,course and time. The sheet is sorted by these 3 fields.If I put in extra column,say something that increases the number by 1 when the time changes,then it should do away with all the indirect columns, I have loads of them in my sheet.Will the $a$26 have to be replaced by the last row in my sheet in the formula e.g $a$7004.

    Also I would like an extra column to to indicate if a contesttant is last,because ranked 4th will be last in some events and rank 14th in others.
    Sorry for so many questions,Dave.

    see attached
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    4th July 2004
    Location
    Canada
    Posts
    2,239

    Re: reverse ranking

    Try the following...

    First, concatenate Columns A, B, C, and D:

    F2, copied down:

    =A2&"#"&B2&"#"&C2&"#"&D2

    Then, to rank lowest to highest...

    G2, copied down:

    =SUMPRODUCT(--(OFFSET(F$2:F$26,MATCH(F2,F$2:F$26,0)-1,,MATCH(F2,F$2:F$26))=F2),--(E2>OFFSET(E$2:E$26,MATCH(F2,F$2:F$26,0)-1,,MATCH(F2,F$2:F$26))))+1

    To rank highest to lowest, change E2> to E2<

    Hope this helps!

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    23rd July 2005
    Posts
    184

    Re: reverse ranking

    Thanks a lot,Domenic.
    Just the 2 questions again,
    Will the $a$26 have to be replaced by the last row in my sheet in the formula e.g $a$7004.
    Also I would like an extra column to to indicate if a contestant is last,because ranked 4th will be last in some events and rank 14th in others

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    4th July 2004
    Location
    Canada
    Posts
    2,239

    Re: reverse ranking

    Quote Originally Posted by pytelium
    Thanks a lot,Domenic.
    You're very welcome!

    Will the $a$26 have to be replaced by the last row in my sheet in the formula e.g $a$7004.
    Yes. In my example, all $26 references would be replaced with $7004...

    Also I would like an extra column to to indicate if a contestant is last,because ranked 4th will be last in some events and rank 14th in others
    Let K1:L4 contain the following table...

    VB:
    Event	Ranked Last 
    1	14 
    2	4 
    3	4 
    
    
    Then use the following formula to indicate which contestant is last...

    I2, copied down:

    =IF(G2=VLOOKUP(D2,$K$2:$L$4,2,0),"Last","")

    Adjust the table and references accordingly.

    Hope this helps!

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    23rd July 2005
    Posts
    184

    Re: reverse ranking

    Domenic

    sorry cant get my head around this last formula at all--putting in last.

    attached sheet,appreciate if you could help.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    4th July 2004
    Location
    Canada
    Posts
    2,239

    Re: reverse ranking

    Have a look at the attached file. You'll notice that I set up a table containing a list of events, along with their corresponding ranking for last place. Expand/amend this list according to your data and change the table reference within the formula.

    Hope this helps!
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    31st January 2003
    Location
    Portsmouth, UK
    Posts
    748

    Re: reverse ranking

    Morning - looks like this has moved on a bit...so, my solution is similar to domenic in that it now uses the combined column F with the formula
    VB:
    =A2&"#"&B2&"#"&C2&"#"&D2 
    
    
    then to rank the various event I use the following in H
    VB:
    =RANK($E2,OFFSET($E$1,MATCH($F2,$F$2:$F$26,0),0, 
    MATCH($F2,$F$2:$F$26,1),1),0) 
    
    
    and to find the last place for each event I would use
    VB:
    =If(H2=MAX(OFFSET($H$1,MATCH($F2,$F$2:$F$26,0),0, 
    MATCH($F2,$F$2:$F$26,1),1),0),"Last","") 
    
    
    in column I

    Replace the $F$26 with the last row (I think this was $F$7004 in your example).

    Let us know if you have any further problems or questions...

    D

    Better a bad day on the water than a good day in the office

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Ranking Formula
    By Andie in forum EXCEL HELP
    Replies: 4
    Last Post: April 23rd, 2007, 23:54
  2. Ranking
    By Rio in forum EXCEL HELP
    Replies: 2
    Last Post: August 6th, 2006, 03:25
  3. Ranking without Gap
    By alizok in forum EXCEL HELP
    Replies: 6
    Last Post: May 4th, 2006, 07:06
  4. Ranking
    By pytelium in forum EXCEL HELP
    Replies: 8
    Last Post: January 6th, 2006, 02:39
  5. Ranking
    By jacknijssen in forum EXCEL HELP
    Replies: 4
    Last Post: May 1st, 2003, 20:15

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
  •  
porno