I am trying to add a formula that will look to a row of dates and return the most recent date that is less than today. For example, say there is a list of paydates and I want to return the most recent. How can this be accomplished.
In the attached, I want the formula in cell D2. Today, it should return 6/27/14, but if the file opens a week from today, then it should return 7/11/14.Excel Forum.xlsx
Last edited by mjhopler; 07-10-2014 at 03:20 PM.
Using your posted file...and assuming the Pay Dates will be in ascending order,
this formula returns the next previous pay date
EDITED TO INCLUDE THIS VARIATION:
Please Login or Register to view this content.
If the formula needs to reference blank cells below the Pay Dates list,
use this formula:
If today is 09-Jul-2014, that formula returns: 27-Jun-2014Please Login or Register to view this content.
Is that something you can work with?
Last edited by Ron Coderre; 07-09-2014 at 03:42 PM.
These work perfect! Now, can you help me understand why?
For the 1st one:
Can you explain why the 1/... part? This portion is the lookup_vector which i understand to mean this is the section is it looking to match the value to return the result_vector. Therefore, the A2:A15 makes sense to me, but i don't follow the 1/ or how Toda() being here works (I do know what the Today() function is).
For the 2nd one:
How does the multiplication come into play?
Thank your for your help!
Looking at this further, the way I would read this is:
Return the value that is less than 2 using range A2:A12 when the numbers in this range are 1 divided by the value, but exclude values greater than today.
If the above is correct, why does the following not work?
=LOOKUP(TODAY(),(A2:A12<TODAY()),A2:A12)
I would read this as return the value that is less than today using range A2:A12, but excluding values greater than today.
Sorry for the multiple posts, but trying to understand.
Taking this one step further, the following does work, so why add the extra coding?
=LOOKUP(TODAY(),A2:A15,A2:A15)
I'll use this formula: =LOOKUP(2,1/((A2:A15<>"")*(A2:A15<TODAY())),A2:A15)
because it addresses both questions
First, when the LOOKUP function is searching for a value that is larger than all of the lookup_range values, it returns the LAST value of the same type (numeric, in this case)
Second, this section: (A2:A15<>"")
test if the referenced cells are not blank and returns an array of TRUE/FALSE values
Example: {TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE}
Third, this section: (A2:A15<TODAY())
tests if the dates in the referenced cells are less than today and returns an array of TRUE/FALSE values
Example: {TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}
Fourth, when a boolean (TRUE/FALSE) value is impacted by an arithmetic operation (+, -, *, /)
TRUE becomes 1 and FALSE becomes 0
When we multiply those two arrays:
{TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE}
X
{TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}
becomes: {1,1,1,1,1,0,0,0,0,0,0,0,0,0}
By dividing those results into 1, we get this result:
{1,1,1,1,1,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!}
We end up with either 1's or errors.
Now this formula:
=LOOKUP(2,1/((A2:A15<>"")*(A2:A15<TODAY())),A2:A15)
resolves to this:
=LOOKUP(2,{1,1,1,1,1,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!},A2:A15)
Since the 2 is larger than the 1's, LOOKUP will match on the LAST numeric 1 and return the corresponding value from A2:A15
I hope that helps.
Last edited by Ron Coderre; 07-10-2014 at 04:19 PM.
You are Awesome!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks