Golf Averages - Last Six Scores

Bob4Golf

New Member
Trying to calculate the average of a row of golf scores ONLY using the last six scores. Realizing that some cells may be empty.

Tried using the following formula but doesn't seem to work
=AVERAGE(index(D4:T4,1,LARGE(if(D4:T4<>"",COLUMN(D4:T4)),6)):T4)

Help this novice

jasonb75

Well-known Member
Running Excell 2010
With excel 2010, both suggestions should work.

The #VALUE! error with CA_Punit's suggestion means that it is probably not array confirmed.

My suggestion doesn't need array confirming, if it is not doing as you need then I would need to know what it is doing wrong in order to try and correct it.

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Bob4Golf

New Member
The formula works fine for me in C4, the problem is most likely your version of excel, however we can not cater for the correct version unless you tell us which one you have.
My suggestion requires 2010 or newer, CA_Punit's suggestion requires 2007 or newer.
My bad - blame the new / novice guy. Yes using an older version of excell. Would that be the only reason these aren’t working?

jasonb75

Well-known Member
Yes using an older version of excell. Would that be the only reason these aren’t working?
Your version is new enough for both suggestions to work (see previous reply).

Bob4Golf

New Member
Perhaps

=AVERAGE(T4:INDEX(4:4,IFERROR(AGGREGATE(14,6,COLUMN(T4:D4)/(D4:T4<>""),6),4)))

Which should give the average of the last 6 (or all if less than 6).
Hey got the formula to work - seems to do exactly what I want. Must have been putting it in wrong. Thanks so very much for all your help. Thanks also for being so patient with this novice and new guy. Thanks again.

Replies
8
Views
122
Replies
5
Views
100
Replies
1
Views
81
Replies
3
Views
54
Replies
3
Views
222