Forum Discussion
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.
=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.
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))
