Rolling average ignoring cells that have text or are blank

[tafkap777]

I have a group with 32 members. Each day, I enter in their scores from that day's completed games. I keep track of their average score for the most recent 10 days, and have typically just manually adjusted the range (for example I manually change A2:J2 to B2:K2).

In playing around with rolling averages today, I was able to successfully find a formula that works, but only if the most recent 10 cells in the row contain numbers. If they are blank or contain text, I get an error.

So my question is, how can my rolling average formula be improved so that it will ignore cells that are blank or contain text. =AVERAGE((OFFSET(A2,0,COUNT(A2:ZZ2)-10,1,10)))

I've tried to paste a picture below of my spreadsheet, but can't. Boy...just fail after fail today.

 

[Aladin Akyurek]

Last 10 cells or last 10 numeric values?

 

[tafkap777]

Last 10 cells. If someone misses a game, I mark that with "miss", and new members will have a blank cell, since they didn't play all 10 games.

I hope this makes sense!<attachment></attachment>

 

[Aladin Akyurek]

Try:

=AVERAGE(OFFSET(INDEX(A2:ZZ2,MATCH(9.99999999999999E+307,A2:ZZ2)),0,0,1,-MIN(10,COUNT(A2:ZZ2))))

 

[tafkap777]

Almost. It works great ignoring text, but if there's an empty cell, I get a #REF !

 

[Aladin Akyurek]

Almost. It works great ignoring text, but if there's an empty cell, I get a [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=REF]#REF [/URL] !

The formula looks for the last cell with a number in it and averages the numeric values if any of 9 cells before that last cell.

Care to post the range and the values it contains for which you get the #REF ! error?

 

[tafkap777]

I wish I could post pics here. Let's call the range A1:A10. I have two guys that have no entry for the first 7 cells...the last three (cells A8:A10) have 10, 16 and 17. This should average 14.3, but instead I get #REF !

 

[Aladin Akyurek]

I wish I could post pics here. Let's call the range A1:A10. I have two guys that have no entry for the first 7 cells...the last three (cells A8:A10) have 10, 16 and 17. This should average 14.3, but instead I get #REF !

You need to adjust the OFFSET bit for the vertical range you seem to have...

=AVERAGE(OFFSET(INDEX(A1:A10,MATCH(9.99999999999999E+307,A1:A10)),0,0,-MIN(10,COUNT(A1:A10))))

 

[tafkap777]

Still not working out. I'm going to see if I can post the actual sheet somewhere and link it here. Won't get to it until tomorrow though.

I really appreciate your efforts!

 

[tafkap777]

Alright. Let's see if you can access this. I uploaded it to google sheets.

https://docs.google.com/spreadsheets/d/1w5qRixe-8T2p5GBYxi_ut1QSkSyZVyIslgdKlEYNpIs/edit?usp=sharing