Announcement

Collapse
No announcement yet.

Plot Chart With Dynamic Range

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Plot Chart With Dynamic Range

    Hi,

    I maintain data which gets updated every week. I have a chart associated with this data which I want to get updated automatically. I tried to use dynamic range on the chart and it kinda works. But the issue with my data is that I have data set in a row, followed by a blank cell which is then followed by average of last two weeks. Hence, when I use a dynamic range, it also displays the last value in the row (average), which I don't want to display in my chart.

    So what I am looking forward to do is set up dynamic range or any other solution, which automatically updates my chart with weekly sales data without showing the average value in the chart.

    I have attached sample worksheet here.

    Please let me know if you know a solution.

    Thanks in advance :-)
    Attached Files

  • #2
    Re: Plot Chart With Dynamic Range

    Adding formula and named ranges based on the data in rows 2:3
    To add new weeks insert cells into F2:F3 and then enter label and value.

    Named range for labels.
    CHT_LABEL: =OFFSET(Sheet1!$A$2,0,1,1,COUNTA(Sheet1!$2:$2)-1)

    Named range for data.
    CHT_DATA: =OFFSET(CHT_LABEL,1,0)

    Series formula using named ranges.
    =SERIES(Sheet1!$A$3,'95590.xls'!CHT_LABEL,'95590.xls'!CHT_DATA,1)

    You also need to adjust the Average formula so it tracks the last 2 items.
    There may be better formula for this.
    =AVERAGE(OFFSET(G3,0,-3),OFFSET(G3,0,-2))
    Attached Files

    Cheers
    Andy

    Comment


    • #3
      Re: Plot Chart With Dynamic Range

      Andy,

      Thanks a lot, this works perfect for me and would save lot of time going forward. Earlier my focus wasn't on "averaging" hence I didn't ask about it.

      Now that you have provided a suggestion, which seems to save some more time for me, let me tell you precisely what I do with average.

      I have sales data for 12 weeks, I have to substract average of latest 4 weeks from previous 4 weeks (trend). Now when I add data for 13th week, the trend should automatically update itself.

      I have attached excel file to show exact scenario. Please let me know if you have a solution for this.
      Attached Files

      Comment


      • #4
        Re: Plot Chart With Dynamic Range

        =average(offset($a2,0,column()-6,1,4))-average(offset($a2,0,column()-10,1,4))

        Cheers
        Andy

        Comment


        • #5
          Re: Plot Chart With Dynamic Range

          Andy, thanks a ton for making my life easier.

          Comment

          Working...
          X