+ Reply to Thread
Results 1 to 10 of 10

Formula required to rank a race

  1. #1
    Registered User
    Join Date
    07-27-2011
    Location
    staffordshire
    MS-Off Ver
    Excel 2003
    Posts
    31

    Red face Formula required to rank a race

    I'm having big problems with the below....

    1) Firstly Column W in the main table, this was intended to rank the riders as the times come in, at the moment it sort of works, however as I am using a Time format in the other columns I cant seem to get it to only rank once a time above 00:00:01 is registered? ie it just ranks regardless, including no time 00:00:00 (a lower time is better).
    (although if possible, it would be most excellent if the rank did not display untill all 6 stages were completed! Perhaps saying "In progress" if not)

    2) I've then been trying to create an auto updating leaderboard (Table A46:C41)
    At the moment I've got it to pull through names in rank order (although the wrong way round!) but I cant get it to pull through the times.

    You help would be much appreciated so that I can help run this race!


    Thanks in advance

    Simon
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    01-25-2014
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    434

    Re: Formula required to rank a race

    not looked at the ranking issue yet, but for the leaderboard, as you've got the names in an order, to get the time can't you just use a vlookup ?

  3. #3
    Registered User
    Join Date
    07-27-2011
    Location
    staffordshire
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: Formula required to rank a race

    Erm yes, i think so, I can get a vlookup that pulls the names, but how would i pull them in order? sorry confused
    Pretty sure you're right and I making it more complicated than it needs to be.

    I would really welcome your suggestion

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Formula required to rank a race

    In V8, copied down, I switched to this formula

    =IF(E8+H8+K8+N8+Q8+T8=0,"",E8+H8+K8+N8+Q8+T8)

    In W8 copied down
    =IF(V8<>"",RANK(V8,$V$8:$V$43,1),"")

    In B47
    =IFERROR(INDEX(B$8:B$43,MATCH(A47,$W$8:$W$43,0)),"")

    In B48
    =IFERROR(INDEX(V$8:V$43,MATCH(A47,$W$8:$W$43,0)),"")
    Questions?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Forum Contributor
    Join Date
    01-25-2014
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    434

    Re: Formula required to rank a race

    @ChemistB

    where you said "In B48"
    it should have been "In C47"

    does the job from what I can see

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Formula required to rank a race

    Yep, thanks for the catch Thirty Two

  7. #7
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,256

    Re: Formula required to rank a race

    Take a look at this.
    Attached Files Attached Files
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  8. #8
    Registered User
    Join Date
    07-27-2011
    Location
    staffordshire
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: Formula required to rank a race

    Thanks brilliant thanks Popipipo and Chemist (Chemist in Column W it still seemed to rank the other way round, but I think I must have done something wrong)

    Just wondering, theoretically they shouldn't get a ranking if they haven't finished ALL 6 stages.

    Should I have a formula in Column X that says "not yet finished", or do you think it is possible in Column V to get the same text to appear. Probably not, because it then would not sort/lookup values in order would it?

    Many thanks again, this is brilliant!

  9. #9
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Formula required to rank a race

    May be this......
    In V8

    Please Login or Register  to view this content.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  10. #10
    Registered User
    Join Date
    07-27-2011
    Location
    staffordshire
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: Formula required to rank a race

    Perfect many thanks

+ 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. Replies: 8
    Last Post: 02-20-2014, 07:03 PM
  2. RANK - Non unique values, contiguous rank required
    By PaulBo in forum Excel General
    Replies: 9
    Last Post: 02-20-2014, 10:34 AM
  3. [SOLVED] Rank required for data
    By bigband1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-19-2013, 01:59 PM
  4. Race points formula
    By shanshine in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-07-2013, 12:52 PM
  5. need help with formula for a 5K race
    By jenjen1972 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-08-2010, 12:47 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