Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Chapel Hill, North Carolina, USA
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Graphs - Connecting the dots (97,2000)

    Is there a way to "force" Excel to connect data points on a graph which are separated by random number of empty cells. We are creating charts of monthly data. Excel does a great job when the data is either monthly or quarterly. But when quarterly data starts in the middle of the period being graphed (i.e. first several quarters of data are missing), Excel only draws data markers, but won't "connect the dots" with a line. Thanks!!!

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Graphs - Connecting the dots (97,2000)

    It is not quite clear what you are aiming at. Is the only time that the data is not contiguous is when the initial periods are without data?

    Are you "fetching" the source data from elsewhere within the workbook to provide the chart range? In that case, you could use an IF formula to refer forward to the first plotted value. Something like:

    IF([source data cell]="", [source data cell next quarter], [source data cell])

    By placing this formula in successive cells of the chart range, you should get a "flat line" to the initial valid chart value.

    If your issue is somewhat different (or more sophisticated), perhaps you could post a suitably censored example. HTH
    Gre

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Graphs - Connecting the dots (97,2000)

    The key to "connecting the dots" is to use #N/A in the empty cells. If you do that, you should see the chart behaving nicely. Of course, you are saying that I have made your data sheet look ugly, so I'll fix that too: select the column of data that includes the #N/A cells, and select the menu Format | Conditional Formatting. In the dialog box, select "Formula Is" from the drop-down and enter =ISERROR(B2) next to that (but change the B2 to the first cell that you have selected). Finally, press the Format button and change Font color to white, and press OK on both dialog boxes.

    That was a little complicated, so I have attached an example. The example also shows how to incorporate the NA function in a calculation. HTH --Sam
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Chapel Hill, North Carolina, USA
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Graphs - Connecting the dots (97,2000)

    Sam,

    Thanks much - that's the trick that I was looking for. I've got two tons of VBA hiding the creating of control charts in the spreadsheet, and this will call for just a bit more, but now I know how!

    Thanks again!!!!

    Larry

Posting Permissions

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