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]

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

In C2 enter and copy down:

AVERAGE(OFFSET(INDEX(D2:XFD2,MATCH(9.99999999999999E+307,$D$1:$XFD$1)),0,0,1,-MIN(10,COLUMNS($D$1:$XFD$1))))

 

[tafkap777]

[FONT=arial, sans, sans-serif]When I enter it in the Google Sheets doc, it produces an error that says, "Function OFFSET parameter 4 value is -9. It should be greater than or equal to 1."

When I enter it into Excel, it works beautifully, but doesn't change when I add in new numbers from todays game. I experimented by adding in a score of 1 in DI16:DI21, but the average in C16 stayed at 22.[/FONT]

 

[Aladin Akyurek]

When I enter it in the Google Sheets doc, it produces an error that says, "Function OFFSET parameter 4 value is -9. It should be greater than or equal to 1."

When I enter it into Excel, it works beautifully, but doesn't change when I add in new numbers from todays game. I experimented by adding in a score of 1 in DI16:DI21, but the average in C16 stayed at 22.

If DI1 has not date, the scores entered DI2, DI3,...,DI16, etc. won't affect any average at all.

The formula is:

=average(offset(index(D2:XFD2,match(9.99999999999999E+307,$D$1:$XFD$1)),0,0,1,min(10,columns($D$1:$XFD$1))))

not:

=average(offset(index(D2:XFD2,match(9.99999999999999E+307,$D$1:$XFD$1)),0,0,1-min(10,columns($D$1:$XFD$1))))​

_{^{<strike>
</strike>}}​

 

[tafkap777]

You are an absolute genius. This works perfectly. I've spent many hours trying to figure this out over the past year or so, and really don't understand how this formula works, but the fact is that it does work.

Thank you so much for your time and help in guiding me through this. I really appreciate it!

 

[Aladin Akyurek]

@ tafkap777

Glad to help. And thanks for the kind words.