Future Date and Lookback average move

[JG101]

Please see file
I would like to calc average price movement based upon number of days from Future date to current date column D, look back average price movement (column C) based upon how many days from Future date -current date in column D.

example:

if it show 7 days in column D, then it will show average price movement past 7 days from Column C (c14:c20).

If column D shows 14, it will look back from that Date, 14 days average price (c19:c32)

thank you in advance for anyone providing help

Future Date and Average move.ods

[jimk]

The following formula produces the string “$C$14:$C$20” in row 14. Drag and fill down to row 19 to produce “$C$19:$C:32”.

=ADDRESS(ROW();3) & ":" & ADDRESS(ROW()+D14-1;3)

To get the average of the values in this range, use INDIRECT.

=AVERAGE(INDIRECT(ADDRESS(ROW();3) & ":" & ADDRESS(ROW()+D14-1;3)))