# Counting the 15 best results from the start

1. ## 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. ## 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. ## 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. ## 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. ## 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.

6. ## 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. ## Re: Counting the 15 best results from the start

Hi Glenn,

New here I will put a sample in this tread!

8. ## 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!!

9. ## 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.

10. ## 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.

11. ## 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. ## 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!

15. ## 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. ## Re: Counting the 15 best results from the start

Originally Posted by Albert310
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. ## Re: Counting the 15 best results from the start

Originally Posted by Albert310
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.

18. ## 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. ## Re: Counting the 15 best results from the start

Thanks BOB, I'm new to this !!

20. ## 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. ## 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. ## Re: Counting the 15 best results from the start

See post #20.

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

24. ## 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}))

25. ## 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. ## Re: Counting the 15 best results from the start

Originally Posted by AliGW
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. ## 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!!!

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!

28. ## Re: Counting the 15 best results from the start

Originally Posted by Albert310
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. ## 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. ## Re: Counting the 15 best results from the start

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

31. ## Re: Counting the 15 best results from the start

Hi Bob,

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

32. ## 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. ## 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. ## 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??

35. ## Re: Counting the 15 best results from the start

Attach the workbook that shows the issue.

36. ## Re: Counting the 15 best results from the start

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

37. ## Re: Counting the 15 best results from the start

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

39. ## 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. ## 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

41. ## 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)))))),"")

42. ## 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. ## 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. ## Re: Counting the 15 best results from the start

Thank you Bob!!

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

#### 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