Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Excel 2010 Chart Trend Line Question

  1. #1
    Board Regular
    Join Date
    Dec 2010
    Location
    Ohio Someplace
    Posts
    1,144
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel 2010 Chart Trend Line Question

    I have a chart with each week of the year for 2015. As the weeks fill with data I would like to have a trend line based on what is there already, how can I get Excel to ignore weeks that have not accrued yet? Even a few weeks left in this year makes a trend line look bad. Thanks
    Rocky
    Excel 2007

  2. #2
    Board Regular
    Join Date
    Dec 2010
    Location
    Ohio Someplace
    Posts
    1,144
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 2010 Chart Trend Line Question

    Anyone know if this can be done? Thanks
    Rocky
    Excel 2007

  3. #3
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,548
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 2010 Chart Trend Line Question

    You could make a dynamic chart that only included values that were present. This uses a defined name that increases in length as the amount of data increases. See Dynamic Charts for an example.

    Or you could change the formula you're using. I presume the problem is you have data in a range, and you use a formula in the chart source data range that fetches the data. It either is a simple lookup like =A2, which returns zero if A2 is blank, or it is an attempt to suppress zero values like =IF(LEN(A2)=0,"",A2), which looks like a blank if A2 is blank. This isn't a real blank, though; it's text, and text is interpreted as zero in a chart. This results in data points along the X axis.

    Use this formula instead:
    =IF(LEN(A2)=0,NA(),A2)

    This returns #N/A in the cell if A2 is blank. Looks ugly, but it isn't plotted, and a trendline in the chart will not include it.
    Last edited by Jon Peltier; Nov 28th, 2014 at 12:59 PM.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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