matthewlouis
Active Member
- Joined
- Mar 28, 2014
- Messages
- 374
- Office Version
- 365
- 2019
- Platform
- 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:
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 |
| |
|