# 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

### 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
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
=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
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
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
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
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
=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
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
Have you confirmed the formula with Ctrl+Shift+Enter