+ Reply to Thread
Results 1 to 9 of 9

Date controlled leader board

  1. #1
    Registered User
    Join Date
    07-27-2017
    Location
    London
    MS-Off Ver
    2010
    Posts
    4

    Date controlled leader board

    Hi Iím looking for some help, and I apologise for my lack of excel know-how.

    I want to create a leader board where I can enter the date into cell Y2 and the leader board will populate in raking order (Lowest to Highest)

    In the example doc I have selected the date of 1/8/17 into Y2 which has taken the results (Total time per day) from B3:U3 and created the leader board showing the members name and time

    For this issue to be resolved the user could change the date in Y2 and the leader board would auto update. For an example please see Sheet2 which shows the date of 2/8/17

    Many thanks in advance for your help.

    Eddie
    Attached Files Attached Files
    Last edited by 12Eddie; 07-27-2017 at 01:27 PM.

  2. #2
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: Date controlled leader board

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn



  3. #3
    Registered User
    Join Date
    07-27-2017
    Location
    London
    MS-Off Ver
    2010
    Posts
    4

    Re: Date controlled leader board

    Hi Glenn,

    Did the changes requested, hope this will suffice. Let me know if not

    Cheers, Ed

  4. #4
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: Date controlled leader board

    Yep. Not as simple an answer as you might wish, though:

    In X6, an array formula, copied down:

    =INDEX($B$2:$U$2,SUMPRODUCT(($B$3:$U$22+1/(COLUMN(B$3:U$22)*10^8)=Y6)*(COLUMN($B$3:$U$22)-COLUMN($B$2)+1)))

    In Y6, another array formula, copied down:

    =IFERROR(LARGE(IF($A$3:$A$22=$Y$2,$B$3:$U$22+1/(COLUMN(B$3:U$22)*10^8)),ROWS($1:1)),"")

    This formula set-up is, however, robust enough to cope with tied results (see orange cells, two names are returned - if this was not catered for, one name would be returned twice...

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-27-2017
    Location
    London
    MS-Off Ver
    2010
    Posts
    4

    Re: Date controlled leader board

    Wow, can't thank you enough I really didn't expect someone to help so much or so quickly.

    I think I'm following the formulas for the most part the one thing I was hoping to clarify is how you selected Y2 to be the input of the date?

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,538

    Re: Date controlled leader board

    Isn't that here?

    =IFERROR(LARGE(IF($A$3:$A$22=$Y$2,$B$3:$U$22+1/(COLUMN(B$3:U$22)*10^8)),ROWS($1:1)),"")
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  7. #7
    Registered User
    Join Date
    07-27-2017
    Location
    London
    MS-Off Ver
    2010
    Posts
    4

    Re: Date controlled leader board

    So it is thanks Ali. Sorry for my ignorance

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,538

    Re: Date controlled leader board

    Don't be sorry! This is how most of us have learnt.

  9. #9
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: Date controlled leader board

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ 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. Automatic Leader board for Swimming
    By Kiiyul in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-04-2017, 08:09 PM
  2. Marking a leader board for my class
    By NicW1 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-09-2017, 04:31 PM
  3. Leader Board for my class data
    By NicW1 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-09-2017, 01:18 PM
  4. Leader Board for my class data
    By NicW1 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-09-2017, 01:15 PM
  5. Leader Board Scores
    By stewart@itsit.com.au in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-09-2014, 07:44 AM
  6. Leader board lookup
    By lochdara in forum Excel General
    Replies: 2
    Last Post: 09-12-2012, 11:27 AM
  7. Racing Leader Board
    By oddicos in forum Excel General
    Replies: 2
    Last Post: 06-09-2012, 07:55 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