Ask Your Question
0

Future Date and Lookback average move

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

JG101 gravatar image

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

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

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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

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

Jim K gravatar image

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

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)))
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 85 times

Last updated: Oct 23 '17