Find the high after a low

[matthewlouis]

I have two dates, one is the date of the high price and one is the date of the low price. Only the high price is shown. My goal is to create a formula that will look in the high price column and find the highest high AFTER a the FIRST low price is made in the high price column. When I look at the table visually, the answer is 93.02, the highest value made since 88.20 0n 3/15/1966. Is there a formula that can automatically find this number?

I tried an array formula =MAX(IF($H$5:$H$24895>$H$2,$I$5:$I$24895)) but that didn't work.

H = Date column
$H$2 = highest price date
$I$2 = Lowest price date
Starting in cell I5 (letter I) is the high price column

Visually, I see 93.02 for several rows but the then it starts dropping as the high drops. I need 93.02 to be the answer.

Here's the data:

From Date​	To Date​
(HIGH PRICE DATE) 2/9/1966 (cell H2)​	(LOW PRICE DATE) 10/7/1966 (Cell I2)​
Column H​	Column I​
(Cell H3) Date​	(Cell I3) High Price​
(cell H5) 2/9/1966​	(cell I5) 94.72​
2/10/1966​	94.70​
2/11/1966​	94.52​
2/14/1966​	94.40​
2/15/1966​	94.04​
2/16/1966​	93.74​
2/17/1966​	93.58​
2/18/1966​	93.14​
2/21/1966​	92.83​
2/23/1966​	92.21​
2/24/1966​	91.81​
2/25/1966​	91.88​
2/28/1966​	91.95​
3/1/1966​	91.65​
3/2/1966​	90.65​
3/3/1966​	90.03​
3/4/1966​	90.25​
3/7/1966​	89.39​
3/8/1966​	89.00​
3/9/1966​	89.21​
3/10/1966​	90.14​
3/11/1966​	89.63​
3/14/1966​	88.92​
3/15/1966​	88.20​
3/16/1966​	88.55​
3/17/1966​	88.60​
3/18/1966​	89.23​
3/21/1966​	89.73​
3/22/1966​	90.28​
3/23/1966​	89.80​
3/24/1966​	89.80​
3/25/1966​	90.14​
3/28/1966​	90.41​
3/29/1966​	90.04​
3/30/1966​	89.57​
3/31/1966​	89.70​
4/1/1966​	90.37​
4/4/1966​	91.33​
4/5/1966​	92.04​
4/6/1966​	92.10​
4/7/1966​	92.42​
4/11/1966​	92.60​
4/12/1966​	92.51​
4/13/1966​	92.81​
4/14/1966​	92.80​
4/15/1966​	92.75​
4/18/1966​	92.59​
4/19/1966​	92.31​
4/20/1966​	92.75​
4/21/1966​	93.02​
4/22/1966​	92.87​
4/25/1966​	92.86​
4/26/1966​	92.77​
4/27/1966​	92.49​
4/28/1966​	91.92​
4/29/1966​	91.86​
5/2/1966​	91.75​
5/3/1966​	91.10​
5/4/1966​	90.11​
5/5/1966​	89.77​
5/6/1966​	88.52​
5/9/1966​	87.96​
5/10/1966​	87.88​
5/11/1966​	88.38​
5/12/1966​	87.49​
5/13/1966​	86.31​
5/16/1966​	86.04​
5/17/1966​	85.03​
5/18/1966​	85.64​
5/19/1966​	86.33​
5/20/1966​	85.79​
5/23/1966​	86.91​
5/24/1966​	87.70​
5/25/1966​	87.48​
5/26/1966​	87.88​
5/27/1966​	87.42​
5/31/1966​	87.65​
6/1/1966​	86.65​
6/2/1966​	86.85​
6/3/1966​	86.55​
6/6/1966​	86.28​
6/7/1966​	85.54​
6/8/1966​	85.43​
6/9/1966​	85.98​
6/10/1966​	86.97​
6/13/1966​	87.59​
6/14/1966​	87.57​
6/15/1966​	87.74​
6/16/1966​	87.18​
6/17/1966​	87.11​
6/20/1966​	87.03​
6/21/1966​	87.28​
6/22/1966​	87.38​
6/23/1966​	87.73​
6/24/1966​	87.31​
6/27/1966​	87.31​
6/28/1966​	86.43​
6/29/1966​	85.98​
6/30/1966​	85.37​
7/1/1966​	86.08​
7/5/1966​	86.41​
7/6/1966​	87.38​
7/7/1966​	88.02​
7/8/1966​	88.04​
7/11/1966​	88.19​
7/12/1966​	87.78​
7/13/1966​	87.06​
7/14/1966​	87.34​
7/15/1966​	87.68​
7/18/1966​	87.59​
7/19/1966​	87.17​
7/20/1966​	86.64​
7/21/1966​	86.24​
7/22/1966​	86.11​
7/25/1966​	85.57​
7/26/1966​	84.67​
7/27/1966​	84.83​
7/28/1966​	84.76​
7/29/1966​	84.30​
8/1/1966​	83.50​
8/2/1966​	83.04​
8/3/1966​	83.71​
8/4/1966​	84.54​
8/5/1966​	84.70​
8/8/1966​	84.31​
8/9/1966​	84.36​
8/10/1966​	83.83​
8/11/1966​	83.53​
8/12/1966​	83.88​
8/15/1966​	83.69​
8/16/1966​	82.71​
8/17/1966​	81.90​
8/18/1966​	81.38​
8/19/1966​	80.78​
8/22/1966​	79.88​
8/23/1966​	79.24​
8/24/1966​	79.63​
8/25/1966​	79.79​
8/26/1966​	77.85​
8/29/1966​	76.24​
8/30/1966​	76.46​
8/31/1966​	78.06​
9/1/1966​	78.50​
9/2/1966​	78.20​
9/6/1966​	78.16​
9/7/1966​	77.26​
9/8/1966​	76.95​
9/9/1966​	76.94​
9/12/1966​	78.34​
9/13/1966​	79.16​
9/14/1966​	79.43​
9/15/1966​	80.60​
9/16/1966​	80.81​
9/19/1966​	80.50​
9/20/1966​	79.90​
9/21/1966​	79.15​
9/22/1966​	78.41​
9/23/1966​	78.43​
9/26/1966​	78.34​
9/27/1966​	79.10​
9/28/1966​	78.36​
9/29/1966​	77.28​
9/30/1966​	77.09​
10/3/1966​	76.98​
10/4/1966​	75.76​
10/5/1966​	76.10​
10/6/1966​	75.09​
10/7/1966​	74.67​

​

​

​

 

[matthewlouis]

Wow, this is quite impressive! Man, you even went and got the historical bear markets!! And RECESSIONS.

You've outdone yourself here, Kirk. This is amazing!!

I will keep tinkering and let you know if I see anything askew.

Thanks so much for this!!

 

[KRice]

Glad to help...
For convenience, you may want to build a table of From/To dates that represent certain time periods of interest. I assembled a notional example drawn from public listings of bear markets and recession periods, as well as a few other periods...in Q4:V46. The drop-down selection cell (A5) uses Data > Data Validation from a list...and that list is presently set to Q4:Q45. Feel free to change the contents of those cells Q4:Q45 and add or delete content from that table (and adjust the Data Validation list range) to make it easier/more intuitive to determine which From/To date set to analyze. If you modify the selection table, keep in mind that the "Select Idx" column is what you'll see when the drop-down selector is used in A5, and the Start and End dates will be copied into B5:C5, and the # of data points in column U will also be copied into D5. Those are the only direct connections between the far left and far right sides of the analysis sheet.

In the cases that I've examined, I haven't encountered any yet where the green points on the high/low price curves are "incorrect"...at least to my untrained eye. Post back if/when you have any questions or discover any issues.

 

[matthewlouis]

Will do!! Great work here, Kirk, much, much appreciated.