Forum Discussion
Use Formula for Cell Reference - Example: Sliding Window Average
Is it possible to use a formula in a cell reference? For example, 'sum of the last 12 cells'. So the cell address might be =SUM($C$(ROW(THIS.CELL)-12):C19) I know this gets into relative cells, but...
If you have 365, presuming the data is in column A. You could use:
=LET(rng,A1:A100,blanks,COUNTBLANK(rng),dynamic,DROP(rng,-blanks),Last_12,TAKE(dynamic,-12),AVERAGE(Last_12))
If there's not 12 numbers to average yet, TAKE will grab what's there and it will be averaged.