How to average best 3 of 5 scores with entries missing

[2020GT]

I'm new here so I'm very sorry if this has been posted elsewhere.

I am needing to track scores and instead of doing a straight average of all scores, I want to average the best 3 out of the last 5 scores posted.

In my example, you can see that I have 7 people who have posted scores. I have also added the average of these scores in the "TOTAL AVE" column. You can see, that since not everyone posted a score every week, there are weeks without scores. So I am looking to use the last 5 scores posted and average the best 3 of them for each person.

Can anyone help me out with this?

I thank you all in advance for your assistance.

[DJunqueira]

I believe this could solve your question:

Please Login or Register  to view this content.

[Estevaoba]

Welcome to the forum.

Try in S2, and copy down:

Please Login or Register  to view this content.

You may have to array enter the formula. If the result is not as expected, please select the cell S2 with the formula, press the F2 key, then press the keys Ctrl, Shift and Enter simultaneously. Then copy down.

Good luck!

[DJunqueira]

I misunderstood the question, 3 best of the last 5... hummm

Please Login or Register  to view this content.

Could be an alternative solution.

[2020GT]

DJunqueira and Estevaoba, thank you for your replies. I tried them all and I believe Estevaoba has the formula that works for my needs. Thank you both for your assistance. I greatly appreciate it.

[Estevaoba]

You’re welcome. Glad to help.
Thank you for the feedback and for the reputation added.
Have a blessed day.