Average and array formula not using duplicate values

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

How about

Formula:

Please Login or Register  to view this content.

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?

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

Formula:

Please Login or Register  to view this content.

A little bit different option:

Formula:

Please Login or Register  to view this content.

Two different other option without take:

Formula:

Please Login or Register  to view this content.

OR

Formula:

Please Login or Register  to view this content.

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

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

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