I'm trying to work on a formula to get the 5 nearest transactions before the specified date.
I've been able to draft the following formula
Where
In Cell F3 I input the following formula to get the closest date before the specified date
In Cell G3 I input the following formula to get the closest name corresponding to closest date before the specified date.
After inserting the formulas I drag them down to G7 so that the SMALL AND ROWS function act as an ascending order sort, and find the nth closest day. eg. For Row F4, it will find the 2nd closest date before the specified date.
Using the above formulas I have been able to get the list of dates and amounts. However the above function creates a problem if there are duplicate dates in the previous 5 dates interval. What happens in such a case, that it returns the duplicate date in the F row correctly, however while in G row, it returns the same name for dates. This I believe is due to match function recognizing only the first match and then returning the name.
Eg;
Consider that our specified date is 15/02/18. And the actual previous 5 transactions are as follows
<tbody>
</tbody>
Now the formula inputted should output the exact same table as above. However it returns the table as
<tbody>
</tbody>
As you can see, the value ABC is posted twice instead of ABC and ASD. This I believe is due to the Match function returning the value of first most row it matches.
The only way this can be overcome is if there was a possibility to get the row numbers of the 5 previous transactions directly from the SMALL FUNCTION.
Hence I wanted to ask if there was a way to do so.
Thanks.
I've been able to draft the following formula
Where
Code:
Range for Dates in Source Table = $C$3:$C$50
Range for Names in Source Table = $D$3:$D:$50
Reference Date (Specified Date) = $F$1
1st Row for the output of closest dates = F3
1st Row for the output of names matching closest date= G3In Cell F3 I input the following formula to get the closest date before the specified date
Code:
IFERROR(INDEX(C$3:C$50,MATCH(SMALL(IF($F$1>=$C$3:$C$50,$F$1-$C$3:$C$50,""),ROWS($F$3:F3)),$F$1-$C$3:$C$50,0)),"")In Cell G3 I input the following formula to get the closest name corresponding to closest date before the specified date.
Code:
IFERROR(INDEX(D$3:D$50,MATCH(SMALL(IF($F$1>=$C$3:$C$50,$F$1-$C$3:$C$50,""),ROWS($G$3:G3)),$F$1-$C$3:$C$50,0)),"")After inserting the formulas I drag them down to G7 so that the SMALL AND ROWS function act as an ascending order sort, and find the nth closest day. eg. For Row F4, it will find the 2nd closest date before the specified date.
Using the above formulas I have been able to get the list of dates and amounts. However the above function creates a problem if there are duplicate dates in the previous 5 dates interval. What happens in such a case, that it returns the duplicate date in the F row correctly, however while in G row, it returns the same name for dates. This I believe is due to match function recognizing only the first match and then returning the name.
Eg;
Consider that our specified date is 15/02/18. And the actual previous 5 transactions are as follows
| Date | Name |
| 14/02/18 | ZYX |
| 13/02/18 | ABC |
| 13/02/18 | ASD |
| 11/02/18 | NYT |
| 10/02/18 | XSD |
<tbody>
</tbody>
Now the formula inputted should output the exact same table as above. However it returns the table as
| Date | Name |
| 14/02/18 | ZYX |
| 13/02/18 | ABC |
| 13/02/18 | ABC |
| 11/02/18 | NYT |
| 10/02/18 | XSD |
<tbody>
</tbody>
As you can see, the value ABC is posted twice instead of ABC and ASD. This I believe is due to the Match function returning the value of first most row it matches.
The only way this can be overcome is if there was a possibility to get the row numbers of the 5 previous transactions directly from the SMALL FUNCTION.
Hence I wanted to ask if there was a way to do so.
Thanks.
