Average and array formula not using duplicate values

[kflemi16]

Hello everyone,

This topic has come up a few times on here, and I have found those answers very useful in creating a golf handicap tracking worksheet.

I am one formula away from perfecting this, due to the help I have found on here and other sites.

The formula I have used to calculate the handicap uses an array to get the lowest 8 scores from the last 20 in a list which can be added to with the last 20 moving down the list as one new score is added. It then averages the 8 lowest from that array.

The issue I have is that it ignores duplicate values and uses the 8 lowest unique values.

Capture.JPG

The formula should include all of the yellow numbers in the image as well as the two orange numbers, but as those are identical it ignores one and goes to the red number, the next highest, which throws out the final number in J25

I have hit the limit of my expertise here, and would greatly appreciate it if someone with more knowledge than me could take a look at this and let me know if what i am asking is even possible and how to to it....

Golf Handicap.xlsx

[Fluff13]

How about

=AVERAGE(TAKE(SORT(TAKE(FILTER(G2:G1000,G2:G1000<>""),-20)),8))

[kflemi16]

Thanks for the suggestion Fluff13.

That throws me a #NAME? error.

I've never used the TAKE function before (never even heard of it tbh), so don't know what this is looking at to diagnose the error.

Probably why my formula looked so complicated compared with yours if the TAKE function works!

Any ideas?

[Fluff13]

Ok, you may not have the TAKE function, especially if your on the semi annual channel.
How about

=LET(f,FILTER(G2:G1000,G2:G1000<>""),r,ROWS(f),AVERAGE(INDEX(SORT(IF(r<20,INDEX(f,SEQUENCE(r)),INDEX(f,SEQUENCE(20,,r-19)))),SEQUENCE(MIN(8,r)))))

[Glenn Kennedy]

Hi.

Is your forum profile showing the Excel version that you need this request to work with?

The best solutions often rely on knowing WHICH version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date. If you aren't sure, in Excel go to File/Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the Version number in your profile (e.g. MS365 (PC) Version 2211). The version number is in the About Excel section further down the Account page.

Cheers,

Glenn.

[HansDouwe]

A little bit different option:

=AVERAGE(TAKE(SORT(TAKE(SORTBY(G2:G999,B2:B999,-1),20)),8))

[Glenn Kennedy]

or:

=LET(r,A2:A32,d,G2:G32,mr,MAX(r),F,FILTER(FILTER(d,MIN(20,mr)),r>mr-MIN(mr,8)),AVERAGE(F))

[HansDouwe]

Two different other option without take:

=AVERAGE(FILTER(SORT(FILTER(SORTBY(G2:G999,B2:B999,-1),SEQUENCE(998)<=20)),SEQUENCE(20)<=8))

OR

=LET(d,G2:G32,AVERAGE(FILTER(SORT(FILTER(d,SEQUENCE(ROWS(d))>ROWS(d)-20)),SEQUENCE(20)<=8)))

[kflemi16]

Thank you everyone for the input.

I guess my organization has an older version which stopped some of the solutions from working.... I have updated in my profile, thanks for the advise Glenn

The two solutions from HansDouwe both worked a treat.

Cannot thank you guys enough!!!

[HansDouwe]

I'm glad it works. You are welcome, thx for the feedback and rep .

[Glenn Kennedy]

And the other suggestions???

[HansDouwe]

@Glenn, your solution in post #7 (15.5....) differs from manual calculation: (=AVERAGE(G13:G14,G16:G17,G20,G26,G28:G29) = 13.4)