Find the maximum value between the specified date range

Question

I have a daily date range in column G and a stock price range in column H. I would like to find the rental 52-week high, that is, the highest share price in column H between the current date and the same date 1 year before.

I use the following formula:

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

Thus, IF conditions set the date range as intermediate between G5 and G5 for less than 12 months and look for the corresponding value in column H.

After entering the formula, I press CTRL+SHIFT+ENTER . It seems to work for the first calculation, but I cannot fill out the formula for the entire date range. I just repeat the same meaning over and over.

Answer 1

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

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

suitable (if you are flexible in leap years!). (Will return # N / A until there is more than 365 days data).

Change The same result, but without errors:

 =IFERROR(MAX(INDIRECT("H"&ROW()-369&":H"&ROW())),"")