Average of Last 5 values from cells with a formula

[roju23]

I have a spreadsheet with loads of data for fantasy basketball. I want to be able to calculate the average of the last 5 games of a player's fantasy scores. My column that records the scores is based on a formula drawing data from other cells and creating an aggregate score. So the points, rebounds, assists, etc calculate to a total. Thus in the column in question, there is a formula to calculate this score. I have found some info on how to get the last 5 values, but it does not work because there are blank cells (by design in the cell formula) and I get a DIV/#0 error. When I delete the cell contents for cells designated for unplayed games, the average formula works just fine. I'm looking for a workaround to get through this.

=AVERAGE(OFFSET(AQ2,COUNTA(AQ2:AQ83)-MIN(COUNTA(AQ2:AQ83),5),0,MIN(COUNTA(AQ2:AQ83),5),1))

This is the formula I found and I need help adapting it for my needs.

The cell formula for the game scores:

=if(AF78=" ", " ", AC78+(O78*0.5)+(W78*1.25)+(X78*1.5)+(Y78*2)+(Z78*2)-(AA78*0.5)+AO78+AP78)

I forced a blank to not have a 0 returned in the cell.

Any help is greatly appreciated.

 

[pgc01]

Hi
Welcome to the board

Try:

=AVERAGE(INDEX($AQ:$AQ;AGGREGATE(14;6;ROW($AQ$2:$AQ$100)/ISNUMBER($AQ$2:$AQ$100);5)):$AQ$100)

 

[pgc01]

P. S.

You may have to change the delimiter in the formula from ";" to ",".