+ Reply to Thread
Results 1 to 5 of 5

Plotting trendline while excluding missing values

  1. #1
    Registered User
    Join Date
    08-01-2017
    Location
    Ljubljana, Slovenia
    MS-Off Ver
    2016 / office365
    Posts
    43

    Plotting trendline while excluding missing values

    Hi,

    I have a dataset that has some missing values for man and women.

    I would like to plot it seperately for man and women & the 2 trendlines should have proper R^2 values and function.

    I have ploted everything - see the attached data, however I cannot get the trendline and the R^2 in the chart to be correct.

    RSQ function calculates proper result and ignorres the missing values, while the plot function doesnt...

    I appreciate the help!
    Andraz
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    7,908

    Re: Plotting trendline while excluding missing values

    First things first. Even if you have all values in column C the trendline will be plotted wrong as your X axis is not linear. You have data 0..15 in 1 increments (so far, so good), but then 17 and 21 (16 and 18,19,20 missing).
    Now the second point - such values of R2 probably mean that there is no significant correlation between values you try to plot. Have you tested the hypotesis about correlation presence? If there is no significant correlation, no regression (trend) line shall be plotted. You can as well draw a random line . But let's skip this point.

    Once you have the missing rows added, you can calculate the regression line with formulas
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    now you can calculate 2 points laying on the regression line. for instance for x1 = -0.5 and x2=21.5 (these are x coordinates of left and right edges of the plot) using simple
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and copied down
    N7 and N8 the slope and intercept values, M10 and M11 x1 and x2
    Now you add new series to the plot. make sure this series is scatterplot type plotted on secondary axis. You select thuis secondary axis on the drawning and delete it. And final touch Add labels to your points using the regression line and Rsquared text:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    or
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    if you (as you probably have in your system) have international character set and ascii 253 is visible as y with dash.

    The ladies part shall be done the same way.
    Attached Files Attached Files
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    03-04-2015
    Location
    Switzerland
    MS-Off Ver
    365
    Posts
    86

    Re: Plotting trendline while excluding missing values

    Hi Kaper,
    this is nice stuff.
    a. the two x points (cells M10 and M11) are just random points or they have any meaning?
    b. I see that the new blue trendline is generated from data that you have in range M10:N11. I can not figure out how you added the new trendline to the graph (the graph is a combination of stacked and a line. probably something simple, but I can not figure it out). please clarify
    and thanks in advnce for you help!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    54,560

    Re: Plotting trendline while excluding missing values

    This thread is marked as solved by the OP. Please start your own thread asking the questions, with a link to this thread.

    Thank you.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Registered User
    Join Date
    03-04-2015
    Location
    Switzerland
    MS-Off Ver
    365
    Posts
    86

    Re: Plotting trendline while excluding missing values

    Ok thanks.
    not sure if just a copy-paste link will do or if there is a smarter way to link solved thread. here is the new thread :
    https://www.excelforum.com/excel-cha...ml#post5339509
    cheers
    Last edited by SwissExcel; 05-28-2020 at 03:35 AM.

+ 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. [SOLVED] Modified LOOKUP Formula for Excluding Missing Lookup Values
    By aaochsner in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-23-2017, 07:29 PM
  2. Convert power trendline equation to formula to fill in missing data
    By X4L in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-26-2015, 04:59 PM
  3. Getting Trendline formulae - results OK when stepping thru code, missing when running
    By thestormdragon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-18-2013, 08:15 PM
  4. Reporting EXCEL trendline Plotting BUG
    By tjohns in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 07-07-2012, 05:07 AM
  5. trendline for arrays with missing data points
    By melnik in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-10-2011, 01:25 PM
  6. Plotting Data against times - Dealing with missing values
    By mynci in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 10-17-2008, 06:49 AM
  7. [SOLVED] [SOLVED] Trendline excluding last 2 data points of series
    By Mary Ann in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 06-15-2005, 09: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