May 19 2020 08:52 AM
I keep a golf spread sheet that has all the points of each round.
For handicapping purposes, we use the average of the last six scores.
Since not every player plays each week, the six scores needed for the average will be different cells.
I need a formula that will average the last 6 entries that have a score entered.
Each week I will add another column and would like it to update the average of the last six rounds with a score.
May 19 2020 12:11 PM - edited May 19 2020 12:14 PM
@scott8111 , For this type of problem, a direct formula even if it could be formed would probably be too complicated, and i at least couldn't think of one. However, i present one approach of doing it step by step like our mind tends to think, i.e. filter out the last six (non-zero) scores and then take their average. The desired calculation could be achieved as in attachment, but as you would note, it is more of a template rather than a formula. The template is expandable, but one need to be careful to expand (drag) the formulas correctly. Also, till you gain confidence of understanding and using it, i would suggest cross-checking the calculated results manually. Step by step way of calculation makes cross-checkings easy, i think!
May 19 2020 12:36 PM
For such sample
formula could be
=AVERAGE(INDEX($E4:AP4,AGGREGATE(14,6,1/($E4:AP4>0)*(COLUMN($E4:AP4)-COLUMN($E4)+1),{1,2,3,4,5,6})))
The only don't place any other data to the right of date columns and take big enough range which covers all potential dates.
May 20 2020 10:19 AM
Thanks for your response.
I will continue to manually count the last 6 cells with scores and use the AVERAGE range of cells
May 20 2020 11:11 AM
@scott8111 , but why manually? Did you note the formula solution given by @Sergei Baklan ?
that is a direct formula , refer to attachment in his post.
(My post also had an attachment which did automation but that was a lenthy way , the formula method of other reply post is direct way.. pls note and try that and if facing any difficulty then feel free to post)