Rolling Average:use INDEX and OFFSET

Oh, I thought I was pretty good with Excel, but I just cannot figure this out... I simply want to average the last 10 values in a column. Every time a new row is added, I don't want to have to update the formula. I figure I have to use INDEX and OFFSET but can't make it work.

For the purpose of this question, you can assume the numbers are in column A starting at row 2. A follow up question might be, "what if there aren't 10 rows of data" but I won't go there at this point.

Please teach me.

Much thanks.

Originally Posted by SKAh

Oh, I thought I was pretty good with Excel, but I just cannot figure this out... I simply want to average the last 10 values in a column. Every time a new row is added, I don't want to have to update the formula. I figure I have to use INDEX and OFFSET but can't make it work.

For the purpose of this question, you can assume the numbers are in column A starting at row 2. A follow up question might be, "what if there aren't 10 rows of data" but I won't go there at this point.

Please teach me.

Much thanks.

Assuming your data start in A2 and no blanks in between

=AVERAGE(OFFSET(INDIRECT("A"&COUNT(A:A)+1),,,-MIN(COUNT(A:A),10),1))

Thanks to both of you. They work perfectly.