how to sum last 6 scores

J

Jennyb7714

Board Regular
Joined
Jan 29, 2008
Messages
78
I have golf scores each week which I need to average the last 6 scores, however not everyone plays every week, so there may be blanks, therefore I need a formula that will average the last actual 6 scores, not the last 6 columns

Anyone have any ideas?
Appreciate any and all help

00 HTML Conversions.xlsm
ABCDEFGHIJKLMNOPQRS
16/05/200913/05/200920/05/200927/05/20093/06/200910/06/200917/06/200924/06/20091/07/20098/07/200915/07/200922/07/200929/07/20095/08/200912/08/200919/08/200926/08/20092/09/2009Average
2
3495758595954544955465650484650.2
43739474334413941353842433941383840.2
559605050524945484853485251564450.7
64945454743424644424540434142.5
7414442414544424541414142444943
843374342434438393740433940414040.5
94644485244464743464640454343434142.5
Sheet2 (2)
 
Last edited by a moderator:

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
Try this array formula

=AVERAGE(INDEX(A3:R3,1,LARGE(IF(A3:R3<>"",COLUMN(A3:R3)),6)):R3)
 
Upvote 0
here's a custom vba function. hold alt+f11 and paste the below code into a new module (insert\module on the menu bar). Close the vba ide. Use the function like any other. E.g. =GolfAvg(A3:R3,6). Note the first part of the function will be your range, in the above case it's A3:R3. The second part of your formula is a number that represents how many games you would like to average (in this case it is six games):

Code: 
Private Function GolfAvg(Target As Range, GameNum As Long)
 
Dim i As Long, c As Range, x As Long, y As Long

Dim GamesArray() As Variant
 
i = 0
y = 0
 
For Each c In Target
    If c.Value <> 0 Then
       i = i + 1
    End If
Next
    
ReDim GamesArray(1 To i)
 
i = 1
 
For Each c In Target
    If c.Value <> 0 Then
        GamesArray(i) = c.Value
        i = i + 1
    End If
Next

For x = UBound(GamesArray) - (GameNum - 1) To UBound(GamesArray)
    y = y + GamesArray(x)
Next
 
GolfAvg = y / GameNum

End Function
 
Upvote 0
Using your posted sample data in A1:R9...

This regular formula returns the average of the last six scores:
Code: 
S3: =AVERAGE(INDEX(A3:R3,LARGE(INDEX((A3:R3<>"")*COLUMN(A3:R3),0),6)):R3)

If there may be less than 6 scores and you want the average of
the available scores, try this:
Code: 
S3: =AVERAGE(INDEX(A3:R3,LARGE(INDEX((A3:R3<>"")*COLUMN(A3:R3),0),
MIN(COUNT(A3:R3),6))):R3)
Copy either formula down as far as you need.

Is that something you can work with?
 
Upvote 0
Thank you both, xld, your formula worked like a charm - I will also try crimson blade's vba version too, but I haven't delved into the depths of vba, but want to....

either of you have any idea on how to do this if someone has less than 6 scores - because at the beginning of the season - they won't have enough scores - the ones that don't have enough is showing #num!

but this is awsome, thank you
 
Upvote 0
awesome - you just answered my question that I just posted about the less than 6 scores, thank you sooooo much
you are awesome
 
Upvote 0
Robust agains any column insertion in front of the first column of data...

Control+shift+enter, not just enter:

=AVERAGE(INDEX(A3:R3,1,LARGE(IF(A3:R3<>"",COLUMN(A3:R3)-COLUMN(A3)+1),MIN(6,COUNT(A3:R3)))):R3)

Note. In case of text other than "" in the target range, replace the A3:R3<>"" test with the ISNUMBER(A3:R3) test.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Very Complex- How to Find Stock Market Probability of 3, 4 & 5 negative days (Find Results of Day 4,5&6)
Replies
8
Views
611
rebel123
R
K
  • Question Question
Average over Last 90 Days - Multiple Sheets
Replies
3
Views
792
KMason17
K
A
  • Solved
Last 6 Events Based on Date
Replies
9
Views
540
Fluff
Fluff
B
  • Question Question
Vlookup or 2d array?
Replies
6
Views
633
base_boot
B

Forum statistics

Threads
1,224,564
Messages
6,179,543
Members
452,924
Latest member
JackiG

Share this page

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