I receive 5-yearly-data for each milestone (e.g. 2010, 2015, 2020...2090), and I want to convert this into a linear annual dataset (2010, 2011, 2012...2090).
My formula already works, but it's inefficient to keep 'rewriting' at every 5 year interval. Is there a better method please?
Currently I am taking the data difference between each 'milestone' and dividing it into the time different between each milestone, and then adding that onto the start value. I drag that across until the end of the 5-years, and rewrite the formula, because it requires absolute cell references ($ signs).
Finally, this is even harder, but what if the dates were actually formatted as dates, rather than as numbers (e.g. 2010 vs 1/1/2010). This makes it even harder, as excel thinks '1/1/2010' is number 40179.
5yearlydata into time series.PNG
See attachment please.
Thank you so much