Dynamic Average of Difference Formula

[skywriter]

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.

[Ron Coderre]

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?

[skywriter]

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.

[Ron Coderre]

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.

[skywriter]

That works.