how to? running average from a variable length list

  • Thread starter Thread starter robreeve
  • Start date Start date
R

robreeve

I am a baseball nut... I have a spreadsheet which tracks ML Baseball and
generates charts and stats. One thing I want to do is maintain a running
'last 10 games' average for each team. The problem is, the way the data is
arrainged in the sheet - by calendar days - every week or so a team has a
day off [or there's the 3 day all star break], so using a strianght AVG or
SUM to find how many wins in the last 10 rows results in inaccuracies in the
count. I could program a VB button to do it programatically, but how would
I make it auto calculate with the sheet, so I dont have to push a button?
Or.. is there any way to make an existing excel function calculate an
average based on a conditionally variable length list... right now in the
wins column a 1 is a win, a 0 is a loss and non playing days are represented
with a text 'ng'. Any help from you folk more guru-like than myself would be
apreciated.. I can post an excellent baseball tracking file...
 
Rob,

The easiest way is to use a user-defined-function. The code below should be put into a standard module in your workbook, and the
function can be used like:

=LastTenAvg(A:A)

to return the average of the last ten 0 / 1 values in column A. Note that if there aren't ten values, then the average of the
values currently entered is returned.

HTH,
Bernie
Excel MVP

Function LastTenAvg(inRange As Range) As Double
Dim myRange As Range
Dim lRow As Long
Dim myCount As Integer

Set myRange = Intersect(inRange, Application.Caller.Parent.UsedRange)

For lRow = myRange(myRange.Cells.Count).Row To 1 Step -1
If myRange(lRow).Value <> "ng" Then
myCount = myCount + 1
LastTenAvg = LastTenAvg + myRange(lRow).Value
End If
If myCount = 10 Then
LastTenAvg = LastTenAvg / 10
Exit Function
End If
Next lRow

LastTenAvg = LastTenAvg / myCount

End Function
 
Back
Top