Dynamic Average of Difference Formula

I don't write many formulas these days and I'm sure this one is obvious to formula experts, but as usual I can't come up with it. Probably something with offset, address......

I have a column of numbers. I want to always lookup the last number. I know I can use lookup(9.99 E.... and get that.
But then I have a cell named period. I want to type a number in that cell and have the dynamic formula average the difference between the last number and the number X cells above that based on the period number.

Example in spreadsheet.

Last four numbers. Period number in cell is 4
69
65
64
62

Formula looks up last number 62, then based on period finds 69 four cells above it calculates the following (62-69)/4 = -1.75.

Thanks.

Using your posted workbook
these formulas perform the calculation you're looking for

Please Login or Register  to view this content.

Are any of those of interest to you?

The first one seems okay with 4, but when I change it to 5, I believe the result should be -1.8 for my sample data, but it returns -1.4. The other two seem okay, so I'm good.

Thanks for your help.

Oops!
This formula:

Please Login or Register  to view this content.

would need to be something like this:

Please Login or Register  to view this content.

That works.