Ask Your Question
0

Future Date and Lookback average move [closed]

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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-18 17:56:41.212481

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

Question Tools

1 follower

Stats

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

Seen: 102 times

Last updated: Oct 23 '17