I have been trying to determine the best way to do this for weeks. Any help would be much appreciated!
The attached spreadsheet calculates both expected and actual ROI for a single project. From that, I need to calculate a 12 month rolling ROI from today's date. To make it slightly more complicated, we only capture ROI for a set amount of time so there will be instances in which, for example it is November '16 so I'm trying to calculate Dec '15 to Nov '16, however, some projects will only have values from Dec '15 to Jun '16.
ALSO, there are values generated for the future that should not be counted into the rolling 12 months until we are actually in the corresponding month.
Excel gurus please help!
Bookmarks