how to? calculate average of a variable length list based on criteria

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

 

[JS]

i think you can do this with Excels built in functions

=AVERAGE(OFFSET($C$20,0,0,-(10+(10-COUNT(OFFSET($C$20,0,0,-10)))),1))

enter formula in a cell below your data - it will average the last 10
numeric entries in the column. Because it counts all the cells that contain
numbers in the column make sure the only numeric cells in the column are the
data you want averaged