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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,959
Office Version
  1. 365
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).
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
934
Office Version
  1. 365
Platform
  1. 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
  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).
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
58,493
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
934
Office Version
  1. 365
Platform
  1. 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
12,959
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
  1. 2010
Platform
  1. 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
  1. 2010
Platform
  1. 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
934
Office Version
  1. 365
Platform
  1. Windows
Have you confirmed the formula with Ctrl+Shift+Enter
 

Watch MrExcel Video

Forum statistics

Threads
1,133,426
Messages
5,658,729
Members
418,467
Latest member
sc356448

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top