4

I have a range of daily dates in column G and a range of stock prices in column H. I would like to find a rolling 52 week high, i.e. the highest stock price in column H between the current date and the same date 1 year prior.

I am using the following formula:

MAX(IF($G$5:$G$10757>=EDATE(G5,-12),IF($G$5:$G$10757<=G5,$H$5:$H$10757)))

So, the IF conditions specify the date range as being in between G5 and G5 less 12 months, and is looking for the corresponding value in column H.

After I type the formula, I press CTRL+SHIFT+ENTER. It seems to work for the first calculation, but I cannot fill the formula down for the entire range of dates. I just get the same value repeating over and over again.

pnuts
  • 58,317
  • 11
  • 87
  • 139
user2209979
  • 159
  • 1
  • 5
  • 10

1 Answers1

3

I think this is possible without an array formula. maybe: 

=MAX(INDIRECT("H"&MATCH(G5-365,G:G,0)&":"&"H"&ROW()),0)

would suit (if you are flexible about leap years!). (Will return #N/A until there are more than 365 days of data).

Edit Same result but without errors showing: 

=IFERROR(MAX(INDIRECT("H"&ROW()-369&":H"&ROW())),"")
Kazimierz Jawor
  • 18,861
  • 7
  • 35
  • 55
pnuts
  • 58,317
  • 11
  • 87
  • 139