I am trying to find a formula that will return the earliest date that a price exceeds a threshold.
Data.xlsx
In the attached example,
Column A - Date
Column B - Price
Cell E1 - Price Threshold
In Cell E2, I'm looking for a formla to return the earliest date that a price exceeds $41 (see cell E1). The result of the formula should be '10/15/02' given the data in columns A&B.
Thanks in advance for any advice.
Maybe this
=MIN(IF(B2:B24>=E1,A2:A24))
Format as date
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Here, try this:
Formula:
Please Login or Register to view this content.
Never use Merged Cells in Excel
Try this array formula
=INDEX(A2:A24,MATCH(1,1/(B2:B24>E1),0))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
If the data can be sorted DEscending by column B (largest dollar value on top)
=INDEX(A2:A24,MATCH(E1,B2:B24,-1))
This would be the most efficient method, if you can sort the data.
cblindsey,
just to explain: mine and AlKey's formula will return same result as long as dates are in ascending order.
But in this case:
Mine will return blue result (because it's first date that match criteria) and AlKey's will return red (because it's minimum date that match criteria).
Please Login or Register to view this content.
Use formula that you rater want to return as result in case date's are mixed.
Thank you. This works perfectly.
I've never seen index_match structured this way. If you don't mind, please explain how the function works.
Thanks,
Chris
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks