+ Reply to Thread
Results 1 to 5 of 5

Dynamic lookback period possible for charts...?

  1. #1
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Dynamic lookback period possible for charts...?

    Hi all,

    Often, when exploring data, it's interesting to look at different lookback periods, i.e., last 10 days, last 100 days or last year.

    In this thread, I was able to learn how to set up my tables such that I can calculate averages and other statistical values with a dynamic lookback period.

    I have partly solved this for charts as well by making a frequency count based on intervals (0-2 points, 2-4 points, 4-6 points, ..., 98-100 points, etc.) and then have the frequency formula set up with OFFSET. Changing the reference cell then automatically updates the chart and as a result also the associated chart.

    However, often, this is not quite ideal and I'd like to actually chart every value or not have to make intervals, etc.

    Is there a (simple) way to set up my charts to have a dynamic lookback period?

    I'm attaching one of my sheets.

    This is not VERY important for me, so don't make any efforts for me at all. I'm just curious if this is at all possible without too much hazzle. I did find some stuff online, but it seemed to relate mostly to dynamic charts in the sense that they were updated when you add a new row.

    Thanks in advance.

    Elijah
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Dynamic lookback period possible for charts...?

    Oh, and never mind that there were 4 charts on that sheet. I simply used what I had at hand. Usually, I only look at one series at once.

  3. #3
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Dynamic lookback period possible for charts...?

    Hi all,

    I'm bumping this with a better example. As can seen in this sheet, the chart labeled # Frequency automatically updates when adjusting the number in cell B3 since it's connected to the frequency count which again is done using the FLOOR function to regulate lookback period.

    Would something be similar to accomplish with the chart labeled data? I.e., have a dynamic lookback period based on for instance the cell value in B3 or some other method?

    Thanks in advance!
    Attached Files Attached Files

  4. #4
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Dynamic lookback period possible for charts...?

    Generally speaking, you don't really apply logic to a Chart. You feed it data, and it charts the data, the end.

    ...So what you do is build an intermediate data step, a "Chart-building table", using either helper rows or another table, and put all the logic and stuff in those, and then feed that into the chart. This is similar to what you did for your #Frequency chart, really.

    See attached.
    Attached Files Attached Files
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

  5. #5
    Forum Contributor
    Join Date
    03-31-2010
    Location
    Norway
    MS-Off Ver
    Excel 365
    Posts
    632

    Re: Dynamic lookback period possible for charts...?

    Wow! That's really clever, Ben!

    Thanks a lot. Also, thanks for the clarification on charts.

    I didn't immediately get the formula, but I get it now. Very nice.

    Sadly, I wasn't able to give you reputation, yet.

+ 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. Replies: 3
    Last Post: 12-30-2017, 04:58 PM
  2. [SOLVED] 24 Hour Lookback
    By teststrip in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-19-2017, 08:51 AM
  3. Lookback and update value
    By bgreeson in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-01-2016, 03:04 PM
  4. 24 hour lookback - flight time tracker for pilots
    By KenBrown in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-16-2012, 07:50 AM
  5. [SOLVED] Excel 2007 : Using date lookback to sum another column?
    By THORmx in forum Excel General
    Replies: 1
    Last Post: 11-09-2010, 08:02 PM
  6. Dynamic chart for time period
    By errorfree in forum Excel General
    Replies: 4
    Last Post: 04-15-2010, 07:16 AM
  7. Replies: 0
    Last Post: 01-19-2005, 06:34 AM

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