I’m having a little trouble with the trendline option in MS Excel. I have a normal data series(X,Y data series) and want to add a linear trendline, but I want to exlude two of the latter points as they have the same Y values and will cause my trendline to have a lower slope then it should.
Is there anyway I can choose to exlude these points from the trendline or is there any way in which I can select a certain number of points for the trendline?
I tried the useless help option but it only told me how to add a trendline in general as was the same when I tried to Google my way out of this problem.
I don’t have Excel available to fiddle with right now, but you should be able to select only particular spots on your grapht. Select a single one (the first one at one end) and hold control while you select the other ones that you want to inclue. Once you’ve done that, right click and select trendline…it might work.
One way to do it for sure is to make the points that you don’t want as a different series…For example, if you have something like this:
1 2
2 4
3 6
4 8
5 16
6 17
Then you can make the 1-4 points as one series on the graph, and the other two as another. In the chart wizard, once you’ve selected the type of graph you want, click on the "series"tab. If it automatically selects something for you, just click remove to get rid of everything, and then click "Add"to create a series. You’ll then see command boxes with a little red-blue button on the right -click on that one for “X axis” and it will kinda minimize, allowing you to go to your spreadsheet and simply highlight everything you want as a series as your X-values (for example, 1 to 4). Do the same with your Y- values (2,4,6,8). Then ADD another series, and select 5 and 6 as your X-axis points and 16 and 17 as your y-axis.
On your chart, you can then select the good series and add the trendline you want, and you can make them look the same by changing the look of the data points.
I hope that was clear enough for you to make sense out of it!
But now here’s my question - what are you doing that allows you to just ignore points like that? Are they obvious outliers? If they are, then just show a statistical proof of that (Q-test maybe?) and then you can ignore them before making your chart at all. If they aren’t outliers, then you should include them in your results, since they ARE what you obtained, and your final results should show this.
I agree that it is not statistically valid to throw out points just because they make for an ugly “trend line”. Is there any other basis for throwing them out? If not, then you have to just swallow hard and go with the data you’ve got, no matter how messy it might be.
Um, guys, I know I’m not a moderator or anything (and I hope I don’t get the title of “junior mod” because of this), but why don’t you leave the guy alone and either answer his question or don’t? Why must you lecture him on statistics like he’s a child or something? The OP asks about Excel, not about the validity of throwing points out when drawing a trendline.
I guess the reason I’m pissed off about this is that a while back I posted a thread asking if anyone knew any futures traders and a bunch of numbnuts responded with “Futures trading is way too risky!!! OMG< DON"T DO IT U WILL DIE!!!11!”
So here, take a couple of these witcha: :rolleyes: