+ Reply to Thread
Results 1 to 4 of 4

Change Dash Type on Trend Line

  1. #1
    Registered User
    Join Date
    11-07-2017
    Location
    Southampton, England
    MS-Off Ver
    2013
    Posts
    5

    Change Dash Type on Trend Line

    Hi all

    I have a line chart with various data on it. I have a trend line and we're mid-way through a period, so only half the data is available.

    Is it possible to change the dash type of the trend line once it goes passed the entered data?

    For example, you have Temperature per day:

    Monday 10c
    Tuesday 13c
    Wednesday 11c
    Thursday
    Friday
    Saturday
    Sunday

    Trend line will be one type of dash until Wednesday, then it will change to a different type to differentiate.

    Thanks

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,810

    Re: Change Dash Type on Trend Line

    I don't think you can do that using the built in trendlines. The way I would do this is to build the regression in the spreadsheet, then use the regression data from the spreadsheet to add my own trendline as a second and third data series. I would expect something like:

    1) A column of 1,2,3,4,5,6,7 for the regression functions.
    2) SLOPE() and INTERCEPT() functions to compute the regression (I'm assuming you want a simple linear regression. If you want a more complex regression, then adjustments will need to be made).
    3) A column to compute the trendline for the entered points.
    4) A column to compute the trendline for the blank points.
    5) Add columns 3 and 4 to the chart as two new data series.
    6) Format the new data series as desired.

    With that overview, where do you get stuck?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,429

    Re: Change Dash Type on Trend Line

    It looks like you can do this by using 2 series and 2 trend lines.

    The second data series is adjusted to only include those cells with data.
    Add trend line to both series.

    The trend line on the complete data set is used to display the dashed trend line segments.
    The trend line on the incomplete data set is used to display the solid trend line segments.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  4. #4
    Registered User
    Join Date
    11-07-2017
    Location
    Southampton, England
    MS-Off Ver
    2013
    Posts
    5

    Re: Change Dash Type on Trend Line

    Eureka!!! Works exactly how I wanted it! Thank you very much

+ 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. Replies: 1
    Last Post: 11-29-2017, 11:34 PM
  2. [SOLVED] identifying shape.type to change colour of line or fill
    By demarc in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-14-2016, 08:56 AM
  3. [SOLVED] How to change the start point of trend line?
    By qzqzjcjp in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 04-22-2015, 07:32 PM
  4. How to change a series chart type from Line to XY-Scatter
    By cartica in forum Excel Charting & Pivots
    Replies: 10
    Last Post: 08-01-2014, 03:08 AM
  5. Control Adobe via Excel - Find line type (i.e. bold vs dash)
    By Kavy1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-08-2012, 10:46 AM
  6. Trend line change
    By Brisbane Rob in forum Excel General
    Replies: 0
    Last Post: 07-14-2006, 03:35 PM
  7. Line type change weight
    By zeeshah in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-02-2005, 10:21 AM

Tags for this Thread

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