# 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

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
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

Replies
6
Views
183
Replies
7
Views
227
Replies
8
Views
952
Replies
5
Views
241
Replies
9
Views
95

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?

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