# Plotting trendline while excluding missing values

1. ## 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

2. ## 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:
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:
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:
Please Login or Register  to view this content.

or
Formula:
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.

3. ## 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!

Thank you.

5. ## 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

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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