Hi, there,
I am looking for a DATE FORMULA that would calculate the last 12 months return of a series of monthly data.
I've tried to nest a date function into a FVSCHEDULE function, but it did NOT worked very well.
I am not very good with DATE calculations, so your help would be very welcomed.
I've attached a simplified sample below.
Thx in advance,
Lula
Give this a try:
In B54 enter =SUM(OFFSET(B46,0,0):OFFSET(B46,-12,0)) Drag across to I54
In B55 enter =SUM(OFFSET(B46,0,0):OFFSET(B46,-24,0)) Drag across to I55
In B56 enter =SUM(OFFSET(B46,0,0):OFFSET(B46,-36,0)) Drag across to I56
Well, I've adapted your formula so to deal with compounding calculations using FVSCHEDULE and tried to automate the process to encompass DATE changes. Then, everytime I change the current month on the YEAR worksheet I'll get the last 12, 24, 36 mths updated on the MONTH worsheet.
=fvschedule(1;DESLOC((procv(Year!A11;datetest;2));0;0):DESLOC((procv(Year!A11;datetest;2));-11;0))-1
But this formula is NOT working.
Pls take a look on the attached sample.
Thx,
Luiz
Your example file refers to functions I don't have, but the attached may get you started on using the OFFSET function (see the rows at the bottom of the Month page). You will need to replacie $A$46 in the formulas with a way to find the right date (i'm a bit confused by your layout and why you want to refer to the month of a date on the year page. Also it would be easier if the day of the month matched).
Actually, the posted formula shows the OFFSET and the VLOOKUP functions in portuguese. I apologise. Here's the "troublesome" formula again:
=fvschedule(1;OFFSET((VLOOKUP(Year!A11;datetest;2));0;0):OFFSET((VLOOKUP(Year!A11;datetest;2));-11;0))-1
As per your questions, the reason I keep a month date on the YEAR page its because that line refers to YTD (year to date) returns and locating it there helps on the comparison with full years above.
On top of that,the (Year!A11) its the only cell that I actually manually change every 30 days. All the other are fixed and that's why I would like the Last 12, 24 and 36 mths returns to be linked to it. So everytime the month is finished I just have to update the Year!A11 cell and "here it go" I would have the Last 12, 24 and 36 mths ready.
Those are the reasons why I'm having trouble using the LOOKUP functions with the OFFSET function you suggested. I suspect it's because they are nested inside FVSCHEDULE.
Appreciate the helping hand!
Lula
The attached formulas will do what you ask. The VLOOKUP formula doesn't work in this case because OFFSET needs a cell refence to offset from. I've used the INDIRECT function to create the cell reference. I also changed the August date on the Year table to match the August date on the Month table so that the MATCH function could find a match (the days need to be the same as well as the month and year even if formatting hides the day).
Presumably you will be adding lines to both the Month and the Year pages, which will make updating the formulas somewhat of a chore. You may wish to use dynamic ranges for the dates on both worksheets. The formulas could then be modified to automatically handle inserted lines. Before doing this, however, you will need to decide where you want the 3 computed series. I have put them on both sheets, because it seemed to me from what you said that you may actually prefer to have them on the Year page. Post back after reviewing this if you would like to see how dynamic ranges can help.
Don’t have an account yet? Register yourself now and be a part of our community!
