+ Reply to Thread
Results 1 to 6 of 6

12 month rolling chart

  1. #1
    Registered User
    Join Date
    09-22-2015
    Location
    Eau Claire, WI
    MS-Off Ver
    2010
    Posts
    65

    12 month rolling chart

    Hi all -

    I am trying to create a dynamic rolling 12 month chart. My data set gets added to each week and I want to view a 12 month snapshot without having to always update the range.

    I believe the offset function is likely my best bet but i'm not sure how to make it work for me. Currently, my formula is:

    ='Raw Data'!$A$1,'Raw Data'!$LY$1:$OB$1,'Raw Data'!$A$3:$A$6,'Raw Data'!$LY$3:$OB$6

    Translation:

    A1-A6 is my Legend
    LY1:OB1 is my date range on the horizontal axis
    LY3:OB6 is my data that is populating on the vertical axis.

    I need both the horizontal and vertical axis to add the most recent data and remove 1 week each time I update this so I only get the annual snapshot. My date increments by week, not day or month.

    Any help would be appreciated.

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: 12 month rolling chart

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

    or you either use offset like you are suggesting or helper data to chart off

    generally speaking offset is neater but helper data is easier to setup/maintain *imo*
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    09-22-2015
    Location
    Eau Claire, WI
    MS-Off Ver
    2010
    Posts
    65

    Re: 12 month rolling chart

    Thank you for your willingness to help. This is in general what I am doing. I just need the graph to auto increment each week to only show an annual snapshot.

    See attachment.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: 12 month rolling chart

    I've attached the solution with a lengthy write up on what i did to achieve the goal


    explaining how the offset works and how it is applied into the chart itself
    Hopefully i did a good enough job explaining it

    I've left it incomplete so you have some try it for yourself

    if there is any gaps in understanding, please let me know what it is
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: 12 month rolling chart

    oh in case your wondering about the other method
    the helper method
    ive attached that too
    it took me about half the time to make..though it doesn't require so much explaining
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    09-22-2015
    Location
    Eau Claire, WI
    MS-Off Ver
    2010
    Posts
    65

    Re: 12 month rolling chart

    These both worked perfect and easy to understand. I really appreciate the extra effort!

+ 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] Turn Dynamic Horizontal Chart into 12-month rolling
    By robbrown in forum Excel General
    Replies: 7
    Last Post: 12-27-2015, 05:22 PM
  2. Create 12 month rolling chart
    By bberger1985 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-19-2013, 12:24 PM
  3. [SOLVED] 12 month rolling chart
    By mcpoogle in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-10-2013, 11:34 AM
  4. Rolling finance chart. Move Data over 1 row when month changes
    By infamouslegato in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 03-13-2013, 03:04 PM
  5. Creating a Rolling 12 or 24 month Chart
    By budvegas in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 11-13-2012, 03:43 PM
  6. Horizontal multi data 12 month rolling chart, multiple sheets.
    By allstar51788 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-29-2012, 08:53 AM
  7. 13 month rolling chart
    By edhughes8 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-11-2008, 09:16 AM
  8. 3 month rolling chart
    By Shanin in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 09-02-2005, 02:05 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