Forum Discussion
Embedded CONCAT in AVERAGE function results in #VALUE! error
I'm trying to dynamically put a variable range of cells in an AVERAGE function to cover different periods preceding the most recent. In this approach I would only have to change the last row number ...
Jos_Woolley Thank you for responding. Seeing your three examples together is helpful.
One more point - it looks like you calculate the same range sizes. They could be defined in any helper range, e.g.
And if in A1 the last row to use, e.g. 2820, formula for 7 days average could be, as suggested earlier,
=AVERAGE(INDEX(D:D,$A$1-$H1+1):INDEX(D:D,$A$1))and drag it down to receive another 4 averages.