Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Lounger
    Join Date
    Sep 2007
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Moving chart for last 30 days (Excel 2003)

    Once again, I need some help. I am looking to make a chart that will plot the previous 30 days info, but it needs to be able to move with the inputting of the new data. Any ideas?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Moving chart for last 30 days (Excel 2003)

    You can create dynamic ranges for the X and Y values. Let's say that the dates are in column A and the corresponding values in column B.
    Put the number of days to be plotted in a cell, say E1.
    In the following, the sheet name is Sheet.

    Use Insert | Name | Define to create a range named XValues with the following definition:

    =OFFSET(Sheet!$A$1,COUNTA(Sheet!$A:$A)-Sheet!$E$1,0,Sheet!$E$1,1)

    and another range named YValues with the following definition:

    =OFFSET(Sheet!$B$1,COUNTA(Sheet!$A:$A)-Sheet!$E$1,0,Sheet!$E$1,1)

    Create a chart, and edit the series definition so that Values refers to =Sheet1!YValues and the Labels for the category axis (X) to =Sheet1!XValues.

    The chart will be updated dynamically as you add (or remove) data, and also if you edit the cell with the number of days to be plotted.

    See attached sample workbook.
    Attached Files Attached Files

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Moving chart for last 30 days (Excel 2003)

    Hi Tyval,

    If you look at the thread starting at <post#=394,337>post 394,337</post#>, it will give you some examples of moving (or scrolling) charts. The attached files contain some examples and some extra ideas to add scroll bars to the chart to scroll the time period back and forth for comparison purposes.
    Regards,
    Rudi

  4. #4
    Lounger
    Join Date
    Sep 2007
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving chart for last 30 days (Excel 2003)

    Thanks for the help again Hans. One more quick question. If I had a file that had all the dates in it for the weekdays of the upcoming year, is there an argument that I can use in the code you provided to only start at days that have a value, and go 30 days back from there?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Moving chart for last 30 days (Excel 2003)

    If you change COUNTA(Sheet!$A:$A) to COUNTA(Sheet!$B:$[img]/forums/images/smilies/cool.gif[/img] in the definition of both named ranges, the chart will display data up to the last filled in value in column B. See attached version.
    Attached Files Attached Files

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Moving chart for last 30 days (Excel 2003)

    Use "COUNTA(Sheet!$B:$B)" instead of "COUNTA(Sheet!$A:$A)" in both named ranges. This will take the count from the values columns and not the date column.

    There must be no blank cells within the dataset of column B or the results will not be as anticipated.

    Steve

  7. #7
    Lounger
    Join Date
    Sep 2007
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving chart for last 30 days (Excel 2003)

    Thanks all of you for the info. I just saw that there was more than one post to my question, slow network here.....

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Moving chart for last 30 days (Excel 2003)

    If there will be blank values within the data of Column B you can use "MATCH(9.99999999999999E+307,Sheet!$B:$B)" instead of "COUNTA(Sheet!$A:$A)" in both named ranges. This will use the last cell in Column B that has a number in it and count back from there...

    Steve

  9. #9
    Lounger
    Join Date
    Sep 2007
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving chart for last 30 days (Excel 2003)

    One more thing. I used the Xvalues and Yvalues and they work great, but I have 4 columns of data that would need to be plotted. Can I modify the formulas to plot the information also? The Dates are in the A column, and the data is in column B,C,D,and E. Any ideas?

    Nevermind... I figured it out... Thanks again though...

  10. #10
    Lounger
    Join Date
    Sep 2007
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving chart for last 30 days (Excel 2003)

    Thought I had it all figured out, but I have found another problem. I used the code "=IF(WEEKDAY(A7)=7,A7+2,IF(WEEKDAY(A7)=6,A7+3,A7+1 ))" to only populate weekdays into my chart, but when I run the 30 day chart, it lists all the days. Is there any way to fix it so that it will only display the days listed on my list, or is there a way to append the above code to make it work?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Moving chart for last 30 days (Excel 2003)

    Please post a copy of your workbook with some dummy data.

  12. #12
    Lounger
    Join Date
    Sep 2007
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving chart for last 30 days (Excel 2003)

    Here it is
    Attached Files Attached Files

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Moving chart for last 30 days (Excel 2003)

    Select Chart | Chart Options...
    Activate the Axes tab.
    Change the Category Axis (X) from Automatic to Category.
    Click OK.
    This forces Excel to plot only the data points provided.

  14. #14
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Moving chart for last 30 days (Excel 2003)

    Hans,
    Can you elaborate on your statement, "This forces Excel to plot only the data points provided."

    I have never had to use the Category or the Time line option. What are these two settings for?
    TX
    Regards,
    Rudi

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Moving chart for last 30 days (Excel 2003)

    Categories are as listed, tehn can be text, numbers, dates, etc. There is one category for each item

    A true "X" is values and will be spaced based on the numbers. A "time" is a special case of the X option.

    For example if you have the numbers:

    1,2,10 as the "x-axis"
    in a category there will be 3 items equally spaced. In a "values", excel knows that 10 is 8 spots from the 2 and the 2 is 1 spot from the 1 so they be spaced appropriately. Excel lets you define the spacing.

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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