+ Reply to Thread
Results 1 to 44 of 44

Counting the 15 best results from the start

  1. #1
    Registered User
    Join Date
    12-29-2019
    Location
    Nijmegen, Netherlands
    MS-Off Ver
    Office 365
    Posts
    23

    Counting the 15 best results from the start

    I’ve created an Excel Workbook that counts scores made bij golfers. The vertical collum is filled with 80 competitors, the horizontal row contains 25 days (for once a week) to fill a result. Each competitor will play but not all dates. The maximum result is produced by the 15 best results on 25 (or less) played days. I cannot nest a formula that will count results untill 15 results are registered and than switch to counting the 15 best results (and leave out the worst results). At the moment I have a SUM-function and a extra cell that gives the number of played days. When the player reaches 15 played days, this extra cell will light up and I have to replace the formula with the formula that counts the 15 best results. It bothers me that I cannot get a formula that combines this. Is there a solution? Thanks for your help!

  2. #2
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Counting the 15 best results from the start

    Try this formula

    =SUMPRODUCT(LARGE(B2:Z2,ROW(INDIRECT("1:"&MIN(15,COUNT(A2:Z2))))))

  3. #3
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: Counting the 15 best results from the start

    First, this is GOLF, so LOWER scores are BETTER. Do you want the highest/worst scores or the best/lowest scores? You use both maximum and best to describe scores, but those are contradictory for golf.

    I'd assume you mean the LOWEST, so BEST, scores.

    If the scores were all different (unlikely for golf), you could use =IF(COUNT(B2:Z2)>=15,SUMIFS(B2:Z2,B2:Z2,"<="&SMALL(B2:Z2,15)),""). However, to limit yourself to just the top 15 scores ignoring ties with 16th or subsequent equal to the 15th,

    =IF(COUNT(B2:Z2)>=15,SUMIFS(B2:Z2,B2:Z2,"<"&SMALL(B2:Z2,15))+SMALL(B2:Z2,15)*(15-COUNTIFS(B2:Z2,"<"&SMALL(B2:Z2,15))),"")

    This only needs 3 SMALL calls. If you'd be willing to use 2 cells for each result, say in columns AA and AB, you'd only need 1 SMALL call.

    AA2: =IF(COUNT(B2:Z2)>=15,SMALL(B2:Z2,15))
    AB2: =IF(AA2,SUMIFS(B2:Z2,B2:Z2,"<"&AA2)+AA2*(15-COUNTIFS(B2:Z2,"<"&AA2)),"")

  4. #4
    Registered User
    Join Date
    12-29-2019
    Location
    Nijmegen, Netherlands
    MS-Off Ver
    Office 365
    Posts
    23

    Re: Counting the 15 best results from the start

    We still play with Stableford result, so the highest scores count. The Netherlands is one of the few countries that still uses Stableford. Stableford will end here in 2021. Sorry I didn’t mention that .

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    31,881

    Re: Counting the 15 best results from the start

    Hi there.

    A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures.

    Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet with some manually calculated expected results.
    Glenn



  6. #6
    Registered User
    Join Date
    12-29-2019
    Location
    Nijmegen, Netherlands
    MS-Off Ver
    Office 365
    Posts
    23

    Re: Counting the 15 best results from the start

    Hi Bob,
    The formula works somewhat like my own. I Build =SUM(G6:AJ6) for the results up to 15 results and than replace it by =SUM(LARGE(G6:AJ6;{1/2/3/4/5/6/7/8/9/10/11/12/13/14/15})) because I didn’t want the total of 16 results.

    The second formula doesn’t function when you register less then 15 results. I tested your formula and I does work but it seems to average the total score to 15 results if played less then 15 games (and that is not what the formula suposed to do).
    Note: I have excel 365 and where you put a “,” I have to put a “;” to let the formula work. Any idea why?

  7. #7
    Registered User
    Join Date
    12-29-2019
    Location
    Nijmegen, Netherlands
    MS-Off Ver
    Office 365
    Posts
    23

    Re: Counting the 15 best results from the start

    Hi Glenn,

    New here I will put a sample in this tread!

  8. #8
    Registered User
    Join Date
    12-29-2019
    Location
    Nijmegen, Netherlands
    MS-Off Ver
    Office 365
    Posts
    23

    Re: Counting the 15 best results from the start

    This is a SMALL example of my Golf Workbook, the sheet in the Workbook where all results are entered. NOTE: the formulas are in Dutch (sorry for that ) I've deleted all connecting formula's to other parts of the Workbook (Hcp, Name, Monthly result, etc) to make it as SMALL as possible
    Thanks for helping out you guys, realy appriciate this!!
    Attached Files Attached Files

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,534

    Re: Counting the 15 best results from the start

    The formulae are not in Dutch for me - remember that they will adjust depending on the locale where your workbook is opened. This is one of the good things about sharing a workbook.
    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.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,534

    Re: Counting the 15 best results from the start

    In AK3 copied down:

    =SUM(LARGE(IF(($G3:$AJ3<>"")*COLUMN($G3:$AJ3)>=LARGE(($G3:$AJ3<>"")*COLUMN($G3:$AJ3),15),$G3:$AJ3),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}))

    Then in F3 copied down:

    =IF(AK3=0,0,AK3/AL3)

    These will need changing for your locale, so I'm attaching the workbook.
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    2,949

    Re: Counting the 15 best results from the start

    Perhaps
    =IF(AL3=0,0,IF(AL3>=15,(SUMIFS(G3:AJ3,G3:AJ3,">"&LARGE(G3:AJ3,15))+LARGE(G3:AJ3,15)*(15-COUNTIFS(G3:AJ3,">"&LARGE(G3:AJ3,15))))/15,AVERAGE(G3:AJ3)))

    you may need to change , to ; depending on your locale

  12. #12
    Registered User
    Join Date
    12-29-2019
    Location
    Nijmegen, Netherlands
    MS-Off Ver
    Office 365
    Posts
    23

    Re: Counting the 15 best results from the start

    AliGW, Thank you!! This problem has haunted me for weeks I can start 2020 with a smile 😊!! Best wishes!

  13. #13
    Registered User
    Join Date
    12-29-2019
    Location
    Nijmegen, Netherlands
    MS-Off Ver
    Office 365
    Posts
    23

    Re: Counting the 15 best results from the start

    Thank you for your reply, davsth, allready got the solution from AliGW, but apriciate your help!
    Last edited by AliGW; 12-30-2019 at 07:40 AM. Reason: Please don't quote unnecessarily!

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,534

    Re: Counting the 15 best results from the start

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

  15. #15
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    2,949

    Re: Counting the 15 best results from the start

    A caution Al3 is
    =COUNTIF(G3:AC3,">0")
    the total results range is to AJ

    It also seems you divide by a count of the values and not 15
    perhaps f3 should be
    =IF(AK3=0,0,AK3/MIN(AL3,15))

  16. #16
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Counting the 15 best results from the start

    Quote Originally Posted by Albert310 View Post
    Note: I have excel 365 and where you put a “,” I have to put a “;” to let the formula work. Any idea why?
    Because you have a Dutch setting for your Excel, and like most of the continent, your Excel uses a semi-colon for a list separator in formulas, and a comma for the decimal separator in numbers. For the UK and US, it is commas and periods.

    As Ali said, when we open your workbook, Excel automatically converts, so that we don't have to worry about it. If you copy a pasted formula you have to adjust it. I should have noted that for you as I saw you were from the Netherlands.

  17. #17
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Counting the 15 best results from the start

    Quote Originally Posted by Albert310 View Post
    The second formula doesn’t function when you register less then 15 results. I tested your formula and I does work but it seems to average the total score to 15 results if played less then 15 games (and that is not what the formula suposed to do).
    My formula only summed, it didn't average. If you want average, use

    =SUMPRODUCT(LARGE(B2:Z2;ROW(INDIRECT("1:"&MIN(15;COUNT(A2:Z2))))))/MIN(15;COUNT(B2:Z2))

    Note, I have adjusted to continental separators for you.
    Last edited by AliGW; 12-30-2019 at 08:31 AM.

  18. #18
    Registered User
    Join Date
    12-29-2019
    Location
    Nijmegen, Netherlands
    MS-Off Ver
    Office 365
    Posts
    23

    Re: Counting the 15 best results from the start

    I thought my problem was solved but it isn't. When I pu tthe formula from AliGW in my sheet and fill more results (testing) and cross the line over 15 results with scores over 18 stablefords the total result over 15 best results is incorrect, presenting a minor (incorrect) total instead of reaching a better result

  19. #19
    Registered User
    Join Date
    12-29-2019
    Location
    Nijmegen, Netherlands
    MS-Off Ver
    Office 365
    Posts
    23

    Re: Counting the 15 best results from the start

    Thanks BOB, I'm new to this !!
    Last edited by AliGW; 12-30-2019 at 09:12 AM. Reason: Please don't quote unnecessarily!

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,534

    Re: Counting the 15 best results from the start

    Just post a new workbook - in it, add a column to show the result you want near to the result that is not what you want. A minor tweak will probably be necessary.

  21. #21
    Registered User
    Join Date
    12-29-2019
    Location
    Nijmegen, Netherlands
    MS-Off Ver
    Office 365
    Posts
    23

    Re: Counting the 15 best results from the start

    Hi davsth,

    You are right, I had a typo there that would become noticed as we reached AC3 , Thanks for that.

    I column F I want to show the average score per played game (and not an average over 15 games). You know how golfers are (and they can look at this sheet in the cloud ).

  22. #22
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,534

    Re: Counting the 15 best results from the start

    See post #20.

  23. #23
    Registered User
    Join Date
    12-29-2019
    Location
    Nijmegen, Netherlands
    MS-Off Ver
    Office 365
    Posts
    23

    Re: Counting the 15 best results from the start

    Hi AliGW,
    I put in the sheet with an extra column and some tekst. Hope that you can solve this
    Attached Files Attached Files
    Last edited by AliGW; 12-30-2019 at 10:38 AM. Reason: Please don't quote unnecessarily!

  24. #24
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,534

    Re: Counting the 15 best results from the start

    Try this:

    =SUM(LARGE(IF(($G3:$AJ3<>"")*COLUMN($G3:$AJ3)>=LARGE(($G3:$AJ3<>"")*COLUMN($G3:$AJ3),MAX(AL3,15)),$G3:$AJ3),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}))
    Attached Files Attached Files

  25. #25
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    2,949

    Re: Counting the 15 best results from the start

    =IF(AL3=0,0,IF(AL3>=15,SUMIFS(G3:AJ3,G3:AJ3,">"&LARGE(G3:AJ3,15))+LARGE(G3:AJ3,15)*(15-COUNTIFS(G3:AJ3,">"&LARGE(G3:AJ3,15))),SUM(G3:AJ3)))

    if you just want the total

  26. #26
    Registered User
    Join Date
    12-29-2019
    Location
    Nijmegen, Netherlands
    MS-Off Ver
    Office 365
    Posts
    23

    Re: Counting the 15 best results from the start

    Quote Originally Posted by AliGW View Post
    Try this:
    ...
    That works!! Strangely it works on one sheet (the copy) and not on the original on onedrive. For me that is not a problem, I will switch to the copy. Than you for getting this done!!

  27. #27
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,534

    Re: Counting the 15 best results from the start

    There is no reason why it should not work on OneDrive - it's working on OneDrive here!!!

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

    Administrative Note:

    Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

  28. #28
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Counting the 15 best results from the start

    Quote Originally Posted by Albert310 View Post
    That works!! Strangely it works on one sheet (the copy) and not on the original on onedrive. For me that is not a problem, I will switch to the copy. Than you for getting this done!!
    My formula gives that result of 260

    =SUMPRODUCT(LARGE(G6:AJ6,ROW(INDIRECT("1:"&MIN(15,COUNT(G6:AJ6))))))

    and it is not an array formula.

  29. #29
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,534

    Re: Counting the 15 best results from the start

    Mine is not an array-entered formula, Bob - it can be entered normally in Office 365.

  30. #30
    Registered User
    Join Date
    12-29-2019
    Location
    Nijmegen, Netherlands
    MS-Off Ver
    Office 365
    Posts
    23

    Re: Counting the 15 best results from the start

    Hi Bob,
    I will test your formula and let you know

  31. #31
    Registered User
    Join Date
    12-29-2019
    Location
    Nijmegen, Netherlands
    MS-Off Ver
    Office 365
    Posts
    23

    Re: Counting the 15 best results from the start

    Hi Bob,

    The result is 1,36846+17 in cell AK6......

  32. #32
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Counting the 15 best results from the start

    I am sorry mate, but that is nonsense. A formula that returns a number will not give 1,36846+17. I tried it on your file, and it gave 260.

  33. #33
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    2,949

    Re: Counting the 15 best results from the start

    Your formula works for me Bob and is more succinct than mine, so a better solution!

  34. #34
    Registered User
    Join Date
    12-29-2019
    Location
    Nijmegen, Netherlands
    MS-Off Ver
    Office 365
    Posts
    23

    Re: Counting the 15 best results from the start

    Hi Bob,

    Sorry I made a typo yesterday
    Today I did it al again but didn't get the result hoped for. I attached a screenshot now, because I can't find out what went wrong, maybe you can??
    Attached Images Attached Images

  35. #35
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,534

    Re: Counting the 15 best results from the start

    Attach the workbook that shows the issue.

  36. #36
    Registered User
    Join Date
    12-29-2019
    Location
    Nijmegen, Netherlands
    MS-Off Ver
    Office 365
    Posts
    23

    Re: Counting the 15 best results from the start

    I've only removed all names, here is the workbook.
    Attached Files Attached Files

  37. #37
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,534

    Re: Counting the 15 best results from the start

    That cell is showing 236 here when I open your file.

  38. #38
    Registered User
    Join Date
    12-29-2019
    Location
    Nijmegen, Netherlands
    MS-Off Ver
    Office 365
    Posts
    23

    Re: Counting the 15 best results from the start

    That is because I uploaded the original file, no changes made.

  39. #39
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,534

    Re: Counting the 15 best results from the start

    I asked you to share the workbook pictured in post #34. There’s no point uploading a workbook that is working! Got to go - happy new year!

  40. #40
    Registered User
    Join Date
    12-29-2019
    Location
    Nijmegen, Netherlands
    MS-Off Ver
    Office 365
    Posts
    23

    Re: Counting the 15 best results from the start

    This is the workbook with the new formula from Bob.
    It works IF a score is placed. If there is no score it will give a error.

    BTW: Happy New Year everybody
    Attached Files Attached Files

  41. #41
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,534

    Re: Counting the 15 best results from the start

    You will need to wrap it in an error trap:

    =IFERROR(your_formula,"")

    The "" can be whatever you want it to return if there is no score.

    =IFERROR(SUMPRODUCT(LARGE(G3:AJ3,ROW(INDIRECT("1:"&MIN(15,COUNT(G3:AJ3)))))),"")

    Remember to adapt for your locale.

  42. #42
    Registered User
    Join Date
    12-29-2019
    Location
    Nijmegen, Netherlands
    MS-Off Ver
    Office 365
    Posts
    23

    Re: Counting the 15 best results from the start

    All's well that ends well! Thank you AliGW and Bob. It works and I'm happy!
    Best wishes for 2020.

  43. #43
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Counting the 15 best results from the start

    Please note, it is not an array formula, so it does not need to be array-entered.

  44. #44
    Registered User
    Join Date
    12-29-2019
    Location
    Nijmegen, Netherlands
    MS-Off Ver
    Office 365
    Posts
    23

    Re: Counting the 15 best results from the start

    Thank you Bob!!

+ 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. Start counting from a name
    By ancstorer in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-06-2018, 10:18 AM
  2. Generating results from two different start dates
    By sjftcec in forum Access Tables & Databases
    Replies: 2
    Last Post: 07-31-2017, 07:05 PM
  3. [SOLVED] Combobox to return results which start with EU
    By nickmax1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-23-2014, 08:30 AM
  4. need formula to start counting plus 450
    By mjcloward in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-12-2013, 04:26 PM
  5. Replies: 6
    Last Post: 02-04-2012, 06:57 PM
  6. Counting with zero between it and start over if..
    By keis386 in forum Excel General
    Replies: 4
    Last Post: 04-15-2011, 09:42 AM
  7. Counting until a condition and then, again counting from start
    By amitraiforyou in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-13-2010, 05:25 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