+ Reply to Thread
Results 1 to 4 of 4

How to incorp missing years into a timeline line graph Xaxis without the values taken as 0

  1. #1
    Registered User
    Join Date
    07-29-2013
    Location
    Leicester
    MS-Off Ver
    Excel 2010
    Posts
    2

    How to incorp missing years into a timeline line graph Xaxis without the values taken as 0

    Hello all, I have a quick query that I would greatly appreciate any help with. I am drawing a line graph to illustrate the overall trends in violence between 1981 and 2012.

    However crime data is only available (was only counted) in certain, sporadic, years - aka there is data for violence in 1981, then the next available lot of data is from 1984, then 1988, then 1990, then 1991 etc - so the data I am looking at has been taken at - and is only available for - uneven intervals.

    I cannot seem to callibrate the x axis to accomodate the unusual intervals of these crime years. I want appropriate spaces for 'missing' years if that makes any sense.

    Currently the line graph spaces the distance between 2001 and 2002 cases equally with the distance between 1984 and 1988 cases etc which I imagine is causing artificially steep peaks and falls in their appearance. I cannot work out how to re-design the x axis to suit these specific needs.

    When I try and add empty columns for the missing years - e.g. introducing empty columns for 1985, 1986, 1987 - the line graphs takes these to be values of 0 - and causes the trend line to dip to zero.

    The spacing works (leaving empty year columns) when drawing scatter plot diagrams which is what I am currently using, but I am desperate to create line graphs for publications etc.

    Thanks for reading.

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How to incorp missing years into a timeline line graph Xaxis without the values taken

    if you create empty columns just put =NA() for all the data in that column =NA() doesn't plot
    eg
    bbbbb vvvv
    01/01/2001 1
    02/01/2001 #N/A
    03/01/2001 2
    04/01/2001 #N/A
    05/01/2001 3
    06/01/2001 #N/A
    07/01/2001 4
    08/01/2001 #N/A
    09/01/2001 5
    10/01/2001
    plots a straight line
    Last edited by martindwilson; 07-29-2013 at 06:12 AM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: How to incorp missing years into a timeline line graph Xaxis without the values taken

    you can add lines to a scatter plot
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  4. #4
    Registered User
    Join Date
    07-29-2013
    Location
    Leicester
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: How to incorp missing years into a timeline line graph Xaxis without the values taken

    Thanks to you both,
    yes both of those options work, I am slightly embarassed as to how obvious that was afterall!

+ 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. Bar graph for text values over years
    By Dimonds in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 03-22-2013, 12:18 PM
  2. VBA code to plot date and time on Xaxis of scatter graph
    By Balachan56 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 10-14-2011, 11:13 AM
  3. Problem charing w/line graph when a series has missing data points
    By tvdij in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 04-08-2008, 04:05 AM
  4. w/ Line graph with time on X and missing data
    By matlack in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-17-2007, 11:49 AM
  5. [SOLVED] missing data points causes my line graph not to connect
    By liebw in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 09-01-2005, 08:05 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