Find the high after a low

matthewlouis

Active Member
Joined
Mar 28, 2014
Messages
263
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Active Member
Joined
Mar 28, 2014
Messages
263
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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!!
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,058
Office Version
  1. 2019
Platform
  1. Windows
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

Active Member
Joined
Mar 28, 2014
Messages
263
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Will do!! Great work here, Kirk, much, much appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,965
Messages
5,621,871
Members
415,862
Latest member
nascaline

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top