I am working in a model where I need to show year-to-date numbers. Does anyone have any good ideas for creating a set of formulas to automate this process? I was thinking about creating formulas for year to date below my financial data and placing unique headings on each column. Then I could run an index match function to bring the most recent quarter’s data into the cells that I want to change (because I have certain output tabs that need to show year-to-date data for reports that I issue each quarter.)
Anyone have a best practice that they would be willing to share on how to do this?
It really depends on the data. But if you have the data organized by date, you could sumif between two dates. And then use today as one date and the first day of the current year (determined by a formula in this formula, or in another cell for a less messy formula). That’s how I’d approach it.
I’m assuming that this is a return series with dates in one column and returns in another. If that’s the case:
The cleanest way I can think of would be to use an array formula like {=PRODUCT(1+returnrange)-1} where returnrange is a range defined by INDEX(MATCH()):INDEX(MATCH()) by matching the begin and end dates.
The simplest might be to use a helper VAMI column and then use _VAMI_toda_y / VAMI_12/31/2014 - 1.