+ Reply to Thread
Results 1 to 3 of 3

aggregate formula to rank each athletes best time the they have multiple times

  1. #1
    Registered User
    Join Date
    09-05-2016
    Location
    Vancouver, Canada
    MS-Off Ver
    365
    Posts
    63

    aggregate formula to rank each athletes best time the they have multiple times

    I am looking to take some race data that has multiple athlete times from training and extract the fastest time from each individual athlete. with each athletes top time it will then need to be ranked amongst the other athletes best times.

    Ideally this would need to be done through an aggregate formula as some of the times potentially could be the same. I would also need to pull the full row of data and put into a new table.

    attached is the sample form the first table is the actual times in no particular order (this table would actually be a lot bigger) to the right is hte table that needs to be populated and below is what the actual final results should be.
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: aggregate formula to rank each athletes best time the they have multiple times

    Please try at

    I5 drag to L5
    =INDEX(A:A,AGGREGATE(15,6,ROW(A$5:A$18)/($E$5:$E$18=$M5),COUNTIF($M$5:$M5,$M5)))
    M5
    =SMALL($E$5:$E$18,N5)
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    09-05-2016
    Location
    Vancouver, Canada
    MS-Off Ver
    365
    Posts
    63

    Re: aggregate formula to rank each athletes best time the they have multiple times

    thanks for the help on this.... one issue that comes up if I drag it down to get more results is it will duplicate the athletes if they have more then 1 time.
    I want it to only show the top time for rider1, rider2, rider3...... and have them ranked in order

+ 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] Formula Syntax for AGGREGATE and Multiple IF Statements
    By fearonc in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-31-2018, 06:45 AM
  2. Adding a time frame formula that ignores multiple times as validation
    By no1freeman in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-09-2016, 11:54 AM
  3. Replies: 4
    Last Post: 12-05-2014, 03:32 AM
  4. [SOLVED] Formula to Rank the number of times a word appears in a column dynamic with Sorting
    By caliskier in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-02-2014, 04:26 PM
  5. > HELP with an (Aggregate) RANK formula
    By collegeitdept in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-26-2013, 04:46 AM
  6. If a search term can be found 1 time, 2 times 3 times 4 times 5 times
    By excelcandy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-10-2013, 09:57 PM
  7. Replies: 0
    Last Post: 09-17-2012, 09:28 AM

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