# Creating annual series from decade data - more efficient suggestion? THANK YOU! :)

1. ## Creating annual series from decade data - more efficient suggestion? THANK YOU! :)

Dear Forum,

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

Thank you so much
Best wishes

2. ## Re: Creating annual series from decade data - more efficient suggestion? THANK YOU! :)

In G6
PHP Code:
``` =10*(G4-2010)  ```
in C13
PHP Code:
``` =10*(YEAR(C12)-2010)  ```
and copy to the right

3. ## Re: Creating annual series from decade data - more efficient suggestion? THANK YOU! :)

Thank you, appreciated, especially the year extraction idea. But it doesn't work if the data isn't the 0-100 numbers I created for simplicity for this sample! Wonder if G6 already has the most efficient formula then?
thanks again though

4. ## Re: Creating annual series from decade data - more efficient suggestion? THANK YOU! :)

Perhaps the following two step proposal will help.
1. Populate G5:Q5 using:
Formula:
`Please Login or Register  to view this content.`

2. Populate G6:Q6 using:
Formula:
`Please Login or Register  to view this content.`

Let us know if you have any questions.

There are currently 1 users browsing this thread. (0 members and 1 guests)