# Average TAKE for rolling dates

Occasional Visitor

# Average TAKE for rolling dates

I am trying to create an excel sheet that averages bottom 13 rows, with new rows being added weekly for new data.

How to I have excel only average the most recent 13 values in a data set, but ignore the bottom values when the cells are empty? My current forumula:

=AVERAGE(TAKE(C3:C66,-13))

C3:C66 is the entire data set, but (for example) I only have cells C3:C15 filled at this time- these are the values I would like averaged. Next week I will enter data into C16, so would like -13 starting at C16.

2 Replies

# Re: Average TAKE for rolling dates

I'd handle it in two steps:

1. Create a dynamic range with TAKE:

``=LET(c, Sheet1!\$C\$3:\$C\$66, nonblank, COUNT(c), TAKE(c, nonblank))``

2. Sheet level formula then becomes:

``=AVERAGE(TAKE(dynamic,-13))``

# Re: Average TAKE for rolling dates

``=AVERAGE(INDEX(\$C\$3:\$C\$66,LARGE(IF(\$C\$3:\$C\$66<>"",ROW(\$C\$3:\$C\$66)),1)-2):INDEX(\$C\$3:\$C\$66,LARGE(IF(\$C\$3:\$C\$66<>"",ROW(\$C\$3:\$C\$66)),13)-2))``

An alternative with older versions of Excel could be this formula. The formula has to be entered with ctrl+shift+enter if one doesn't work with Office 365 or Excel 2021.