Average TAKE for rolling dates

Copper Contributor

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:


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


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:




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.