Using average

Copper Contributor

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.

4 Replies

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

@scott8111 

For such sample

image.png

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.

Thanks for your response.

I will continue to manually count the last 6 cells with scores and use the AVERAGE range of cells

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