I would like Excel to review a row of cells containing gift dates, amounts and fund names. I would like Excel to select the gift which occured closest to the date and display the amount, the date and the fund. See example. Is there a formula available? forum question nearest date 1.0.xlsx
Which date ... today?
Does this do what you want in R3? You can drag it over to S3 as well.
Please Login or Register to view this content.
Some people volunteer in soup kitchens or hospitals. I choose to make the world better by trying to help you with Excel. We're all learning.
<---Click * Add Reputation for all helpful comments. It's like giving a smile.
Forum Rules: How to mark your post [Solved] and have a happier, Excel enriched life.
I advice you to store the data tablewise (in rows).
This makes it a lot easier to analyze the data (e.g. with a pivot table).
In yellow a helpcolum.
The result is made with index / match
In green the result.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Sorry ... I missed your reference date
I'll second the data rearranged...
(no helpers in this solution; uses array formulas which require Ctrl+Shift+Enter entry to enact)
@oeldere
Could you please shorten your file naming. I realize that's how it downloads from here for you, but when I (we) download your file, the forum appends another set of reference text in front of yours. My pooter pooters out saying it can't find file... but I believe it is because the name/path is too long.
I third oeldere's example, I would just change the helper column formula to this:
So you get the closest date in either direction.Please Login or Register to view this content.
Here the same file as in #4; now with a shorter name.
That works...
And thus, I agree with Nigel on the change if using a helper column.
It just happens to work out as is because 5/2/2014 is the earliest date in the list and the 5/1/2014 reference date is before it. Change the reference date to a later date, say 7/2/2014... and it don't work as is.
@Jhren and nigelbloomy
Please Login or Register to view this content.
Totaly right, the formula needs the amend nigel suggested.
Thanks for pointing me on that one.
Thank you, experts, for your suggestions. I am trying to understand them. Being much less than an expert myself, I have a few questions: 1. I don't think my data allows me to use a helper column and additional rows. I attach part of the actual 200 row data file. I want to generate a column of results, so don't seem to have room for the helper column. 2. Also, and I don't think I mentioned this, I want the results to include not only the date and amount but the fund designation. See attached in red. Thank you for any further help you can offer.
You don't add the file.
I also don't see the attched in red.
1) yes there is room for a helpercolumn or helperroom, so that will not be the problem.
Thanks for the quick reply. I forgot to attach the example. Now I can't find the "attach icon". I see that the IMG code is off. Do I need to turn it on somewhere?
Trying again.
Last edited by zbor; 06-29-2015 at 03:01 PM. Reason: wrong list posted
@bwiebe
If there are confidential information on sheet 2, please remove the sheet.
Please also add the expected result in your file, and refer the related cells with the same color.
Last edited by oeldere; 06-29-2015 at 01:03 PM.
Use this document, please. revised question nearest date 1.0.xlsx
Does this work for you?
The formula in BS2 is an array formula so you have to press CTRL+SHIFT+ENTER instead of just ENTER to enter it. After that you can drag it down the column as far as you need.
Using Nigel's file, here's an alternate ARRAY formula for BS2...
NOTE: Formula assumes date entries are in chronological order from left to right.Please Login or Register to view this content.
Last edited by jhren; 06-29-2015 at 04:14 PM.
This ARRAY formula will return first date after reference, regardless of order...
Please Login or Register to view this content.
@Jhren I like the use of iferror, 1/0, and mod to pull in only the date columns. I know my formula worked, but it was ugly because I had to select each of the date columns. Yours works very well.
Thanks, Nigel.
I was originally trying to use OFFSET(), which returned the dates... but kept getting a #VALUE error when trying to process any further.
BTW, an alternative for that IFERROR, 1/0 part is...
IF((($AE2:$BF2>F2)*(MOD(COLUMN($AE2:$BF2),3)=1))=1,$AE2:$BF2,"")
Thank you kindly, all, for your expert contributions. I have tried them in the current version of the spreadsheet I am working with. My level of expertise, I thought, extended to plugging in the appropriate cell numbers to fit the revised spreadsheet. Unfortunately, I am not getting the correct results. I have checked this numerous times. In part it may have to do with the type of number I am selecting. I have tried date, currency and general, to no avail. I would welcome further help, which no doubt is to point out some simple error I am making. See attached. Thank you. revised question nearest date 1.6.xlsx
Does this formula fix it?
It looks like the number of columns was a little different in the example file vs this one. The mod part of the formula needed to look at those that =0 instead of those that =1. This is still an array formula.Please Login or Register to view this content.
AS5, ARRAY formula...
...because the column number of the first cell (O5=column 15) and every third cell to it's right is equally divisible by 3Please Login or Register to view this content.
AT5, regular formula...
AU5, regular formula...Please Login or Register to view this content.
Please Login or Register to view this content.
PS: If you are going to change columns from time to time, change the 0 to MOD(COLUMN($O5),3)... e.g.:
Please Login or Register to view this content.
Last edited by jhren; 06-30-2015 at 12:14 PM.
Thank you very kindly. This is working. I have a much improved report.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks