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!

Try this formula

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

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)),"")

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 .

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?

Hi Glenn,

New here I will put a sample in this tread!

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

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

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

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

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.

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

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 ).

=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

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

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.

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

Hi Bob,

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

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.

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

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

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

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

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

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

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

Thank you Bob!!