+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    10-20-2020
    Location
    London
    MS-Off Ver
    16
    Posts
    2

    Exclamation 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

    See attachment please.
    Thank you so much
    Best wishes
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    880

    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. #3
    Registered User
    Join Date
    10-20-2020
    Location
    London
    MS-Off Ver
    16
    Posts
    2

    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. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,724

    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: copy to clipboard
    Please Login or Register  to view this content.

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

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Help Creating chart that counts the number of excel entries in a date range (decade)
    By Indywar2 in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 08-17-2020, 02:15 AM
  2. Creating a more efficient data matching macro
    By Ouka in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-14-2017, 06:48 PM
  3. Replies: 2
    Last Post: 11-11-2013, 05:37 PM
  4. Efficient way of creating summary table from input data file
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-28-2013, 06:40 AM
  5. Creating a Product Suggestion/Recommendation Form
    By BPauly in forum Excel General
    Replies: 8
    Last Post: 10-10-2012, 06:00 PM
  6. Calculating Average Annual Returns from a Series of Annual Returns
    By Bruinsfan in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-03-2012, 09:50 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1