Ok, I tried searching for this and eventually found some results, but thought I’d put up a piece to make it a bit easier for people to find.
Basically we want to select a certain number of cells within a range.
We can do this using the =offset() formula.
Simply use the optional height or width parameters. You can use a negative value to go up or left.
This s really useful for selecting say 365 cells instead of counting all the row numbers.
So very useful if you want say the average over the year: =average(offset(reference cell,0,0,-365))