Average of last x rows in Calc?

In a spreadsheet to which new rows are added continually, is there a way to write a formula that’ll automatically calculate the average (or anything else, for that matter) of the values in the last x rows of a specific column?

You can define last X cells as

OFFSET($A$1;COUNTA($A$1:<any big address at column A>)-X+1;0;X;1))

Therefore, the desired formula will be similar to this design (for X=10)


Of course, this formula will work correctly only if all the cells in column A has a value (no missing data, empty cells)

Belated thanks for your very useful answer.