Ask Your Question

Future Date and Lookback average move

asked 2017-10-22 14:15:04 +0100

JG101 gravatar image

updated 2017-10-22 19:11:28 +0100

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.


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

C:\fakepath\Future Date and Average move.ods

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-10-23 15:14:10 +0100

Jim K gravatar image

updated 2017-10-23 15:14:57 +0100

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-10-22 14:15:04 +0100

Seen: 84 times

Last updated: Oct 23 '17