Tuesday, November 5, 2013

yogi_Compute Row By Row Average Score of Specified Number Of Games Played Last (skipping blanks which mean game not played)

                                          Google Spreadsheet   Post  #1413
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Nov 05, 2013
question by Mark Dunfee (http://productforums.google.com/forum/?zx=77lg8d3u1x5#!category-topic/docs/spreadsheets/lPWHgTER8tw)
Calculate the average of X number of the last filled cells (to infinity)
Hi, I'm trying to make a sheet where I keep track of my athletes' bowling scores (I'm the coach).  I want to be able to find the average across the row (easy), but also have the option to have the average of their last X games (X is A2 on the sheet).  I've searched around and found some close solutions (which you can see on my sheet), but even this one doesn't skip blank cells.  Sometimes a particular bowler won't have a score on a given day and I still want their "last X games" average to be factual. I want to be able to add the most recent games on the end of the cell.  I had written a script to add a column in right after the averages upon hitting a button, but that caused other problems (and wouldn't have solved the blank cell issue either).

So I need a formula to calculate the average of X number of the last filled cells, without a limit to how many columns there are (infinite scores).

Thank you for your time.

https://docs.google.com/spreadsheet/ccc?key=0AhYeFeb9aty2dHpMb2tDU2hBTlB0bGQ2SkVxbVp4Vnc&usp=sharing

So when A2 is "2" the value in C5 should be 6 (average of 4 and 8) but it's currently 4 (the average of 8 and the blank cell before it).

-------------------------------------------------------------------------------------------------------------------------------------