Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Mar 2010
    Location
    South Wales
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts
    When plotting a time series on a line graph in Excel 2002 the last date point won't show if the number of points doesn't divide into the increments allowed. Is there a fix whereby I can get Excel to show the last date point rather than applying a text box and manual tick mark?

    Any help appreciated!

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    I'm using 2007 - but if you Format the Axis, setting the minimum and maximum values does that do what you want?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    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
    Catherine,
    I presume the problem is akin to having an X-axis range from 0 to 22 and incrementing by 5. Your X-Axis will display 0, 5, 10, 15, 20 but not the max value of 12....

    [though i could be wrong...]
    Steve

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Could be - but in that case one could set the increment of the axis.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    Lounger
    Join Date
    Mar 2010
    Location
    South Wales
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks both. Unfortunately there isn't a min/max facility in the 2002 version (or I couldn't find one in the Format axis pop-up) and the time series is from 1978 to 2009 (42 data points) which doesn't allow for an incremental split that gives 2009 as the last data point on the axis.

    Looks like I'll have to wait for our organisation to upgrade and use the typesetting fix in the meantime.

  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
    You can increment by single years then every year should be listed or increment by 31 years and just the first and last will be listed....

    Personally I would prefer to start with 1975 and increment by 5 years. You can put the minor ticks as 1 year apart. Most people will be able to interpolate and figure out the pattern...

    Steve

  7. #7
    Lounger
    Join Date
    Mar 2010
    Location
    South Wales
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    You can increment by single years then every year should be listed or increment by 31 years and just the first and last will be listed....

    Personally I would prefer to start with 1975 and increment by 5 years. You can put the minor ticks as 1 year apart. Most people will be able to interpolate and figure out the pattern...

    Steve
    Thanks Steve. If I increment by single years the X axis will be way too cluttered and the year markers would overlap, so it's not an option unfortunately. Neither is first and last years as the graph would look incomplete.

    Our time series starts at 1978 (earliest data available) which has to be included so I can't even start at 1980.

  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
    What about showing every year and decreasing the font size of the year or rotate it so it prints sideways, or even formatting as '00 to display line '80 instead of 1980?

    Steve

  9. #9
    Lounger
    Join Date
    Mar 2010
    Location
    South Wales
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    What about showing every year and decreasing the font size of the year or rotate it so it prints sideways, or even formatting as '00 to display line '80 instead of 1980?

    Steve
    Unfortunately our publication has a very specific style that we have to stick with i.e. font size, orientation and, in this case, years have to be in full. Looks like for now we'll have to rely on a typesetter fix until we get the upgrade. Thanks for trying though Steve!

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    No point in waiting for an Excel upgrade I expect, any excel version allows you to define the min and max value of the category axis, but only for a scatter chart, not for a line chart.

    Note, that if you use a line chart and your values contain gaps (e.g. a year is missing), those are not represented proportionally in a line chart, the points show up equally spaced.

    Try if you can get what you need by changing your chart type to xy scatter.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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