Golf Averages - Last Six Scores

Bob4Golf

New Member
Joined
Feb 29, 2008
Messages
14
Office Version
2010
Platform
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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,996
Office Version
2019
Platform
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).
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
616
Office Version
365
Platform
Windows
=AVERAGE(index($D$4:$T$4,1,LARGE(if($D$4:$T$4<>"",COLUMN($D$4:$T$4)-COLUMN($D$4:$D$4)+1),6)):$T$4)


Considering the Range from D4:T4
 

Bob4Golf

New Member
Joined
Feb 29, 2008
Messages
14
Office Version
2010
Platform
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).
Sorry thils didn’t do the trick. Data is in cells starting in D4 through T4. I want the average to go in cell C4. Again needing the last six scores, realizing that some cells may be empty. Also using older version of excell
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,786
Office Version
365
Platform
Windows
Also using older version of excell
What version are you using?
Please update your account details to show this, as it affects which functions you can use.
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
616
Office Version
365
Platform
Windows
Confirm the formula with Control+Shift+Enter

Also if there are less than 6 Values, within the range then it will work
=AVERAGE(IFERROR(INDEX($D$4:$T$4,1,LARGE(IF( $D$4:$T$4<>"",COLUMN($D:$T)-COLUMN($D:$D)+1),{1,2,3,4,5,6})),""))
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,996
Office Version
2019
Platform
Windows
Sorry thils didn’t do the trick. Data is in cells starting in D4 through T4. I want the average to go in cell C4. Again needing the last six scores, realizing that some cells may be empty. Also using older version of excel
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.
 

Bob4Golf

New Member
Joined
Feb 29, 2008
Messages
14
Office Version
2010
Platform
Windows
=AVERAGE(index($D$4:$T$4,1,LARGE(if($D$4:$T$4<>"",COLUMN($D$4:$T$4)-COLUMN($D$4:$D$4)+1),6)):$T$4)


Considering the Range from D4:T4
Gives me a #VALUE! Statement
 

Bob4Golf

New Member
Joined
Feb 29, 2008
Messages
14
Office Version
2010
Platform
Windows
Confirm the formula with Control+Shift+Enter

Also if there are less than 6 Values, within the range then it will work
=AVERAGE(IFERROR(INDEX($D$4:$T$4,1,LARGE(IF( $D$4:$T$4<>"",COLUMN($D:$T)-COLUMN($D:$D)+1),{1,2,3,4,5,6})),""))
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 newbie here. Running Excell 2010
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
616
Office Version
365
Platform
Windows
Have you confirmed the formula with Ctrl+Shift+Enter
 

Watch MrExcel Video

Forum statistics

Threads
1,102,562
Messages
5,487,578
Members
407,605
Latest member
PACULA

This Week's Hot Topics

Top