Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Plot Chart With Dynamic Range

  1. #1
    Join Date
    25th March 2008
    Posts
    10

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,313

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Cheers
    Andy


  3. #3
    Join Date
    25th March 2008
    Posts
    10

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,313

    Re: Plot Chart With Dynamic Range

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

    Cheers
    Andy


  5. #5
    Join Date
    25th March 2008
    Posts
    10

    Re: Plot Chart With Dynamic Range

    Andy, thanks a ton for making my life easier.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 9
    Last Post: February 5th, 2009, 00:59
  2. Replies: 10
    Last Post: May 17th, 2008, 18:06
  3. Replies: 1
    Last Post: April 3rd, 2008, 10:30
  4. Plot x Values At a Time in Dynamic Chart
    By ss99 in forum EXCEL HELP
    Replies: 4
    Last Post: September 26th, 2007, 17:58
  5. Chart/Plot Changing Range
    By error#9 in forum EXCEL HELP
    Replies: 5
    Last Post: October 25th, 2006, 18:50

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
  •  
porno