Average TAKE for rolling dates

[thays1090]

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]

@thays1090 

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))

[#3]

@thays1090 

=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.