Forum Discussion
Using average
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.
6 Replies
I’m looking for one like the samples but not necessarily using dates. Just go to players name, enter that score of that day and get the new average. We use 6 round average also
For the legacy Excel 6-years old answer in in this thread below. For the modern Excel that could be like
=LET( CellsWithData, TOROW($E10:AE10,1), AVERAGE(TAKE(FILTER(CellsWithData, CellsWithData<>0),,-6)) )
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.
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!
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 SergeiBaklan ?
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)
