Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to? running average from a variable length list
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... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to? running average from a variable length list
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 "robreeve" wrote in message news:i4yUa.133991$GL4.34911@rwcrnsc53... 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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable Length Columns | Excel Discussion (Misc queries) | |||
Variable Axis Length | Charts and Charting in Excel | |||
Return a Variable value length | Excel Worksheet Functions | |||
Transpose a variable length list into Excel / Access Table | New Users to Excel | |||
Sum a column of variable length? | Excel Discussion (Misc queries) |