find first and latest dates that equal the value today

Cheeks1969

New Member
Joined
Dec 7, 2016
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Let's say I get a datapoint of something that fluctuates over time and I enter it with a date down two columns.

I want to find the last date where it was equal to, or less than the current value. And I want to find the first time it was equal to or higher than that value.

An example might be the stock market. If I had the weekly closing Dow Avg as the data point and the date of every friday in the date column.
Say the market goes up and down and now it's crashing. I enter the closing price today and want to use that to find when it was last at this level, and when it was first at this level.

I found a solution that finds the last match, but nothing that would find the date it was last < or =. For the "first time it hit this level" it would have to be the first datapoint that is >= than current.

Thanks
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
C3: =IFERROR(INDEX(A$2:A2,MATCH(TRUE,B$2:B2>=B3,)),"-") array-entered
D3: =IFERROR(LOOKUP(2,1/(B$2:B2<=B3),A$2:A2),"-")


Book1
ABCD
1DatePriceFirst >=Last <=
24 Jan 2019980
311 Jan 2019988-4 Jan 2019
418 Jan 20191,001-11 Jan 2019
525 Jan 20191,005-18 Jan 2019
61 Feb 201999018 Jan 201911 Jan 2019
78 Feb 201999818 Jan 20191 Feb 2019
815 Feb 20191,00425 Jan 20198 Feb 2019
922 Feb 20191,007-15 Feb 2019
101 Mar 20191,009-22 Feb 2019
118 Mar 20191,016-1 Mar 2019
1215 Mar 20191,017-8 Mar 2019
1322 Mar 20191,00722 Feb 201922 Feb 2019
1429 Mar 20191,00018 Jan 20198 Feb 2019
155 Apr 201999918 Jan 20198 Feb 2019
1612 Apr 201999718 Jan 20191 Feb 2019
1719 Apr 201998918 Jan 201911 Jan 2019
1826 Apr 20199804 Jan 20194 Jan 2019
193 May 20199754 Jan 2019-
Sheet1
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

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
Back
Top