Golf Averages - Last Six Scores

Bob4Golf

New Member
Joined
Feb 29, 2008
Messages
14
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
10,986
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Some videos you may like

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
Joined
Feb 29, 2008
Messages
14
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
10,986
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Feb 29, 2008
Messages
14
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,907
Messages
5,544,984
Members
410,647
Latest member
LegenDSlayeR
Top