lock a trend line and re-plot the outlier without effecting the trend line

[summerwa]

I have a small data set with Fiscal years and Sales. One of the data points is an outlier so in order to get an accurate trend line I have omitted the outlier data point. I have already created a trend line omitting the outlier.


What I want to do:


1) Lock the trend line and re-plot the outlier without effecting the trend line
2) Find the % difference between between each fiscal year and the trend line.


My data set is so simple the regressions and line formulas seem to be complicating this task. Thank you for your time.

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Fiscal Year[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]FY 09[/TD]
[TD]91434[/TD]
[/TR]
[TR]
[TD]FY 10[/TD]
[TD]98996[/TD]
[/TR]
[TR]
[TD]FY 11[/TD]
[TD]139821[/TD]
[/TR]
[TR]
[TD]FY 12[/TD]
[TD]303181[/TD]
[/TR]
[TR]
[TD]FY 13[/TD]
[TD]229622[/TD]
[/TR]
[TR]
[TD]FY 14[/TD]
[TD]263403[/TD]
[/TR]
</tbody>[/TABLE]

 

[tybaltlives]

I just added another sales column with the outlier in that in FY12. And deleted the Sales value for FY12 in the original sales column. Also, plotted without lines, just data point. Then did the regression.

Excel 2010
	A	B	C	D	E	F	G	H
5		sales	Outlier
6	FY 09	91434
7	FY 10	98996
8	FY 11	139821
9	FY 12		303181
10	FY 13	229622
11	FY 14	263403
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Sheet1

 

[Trouttrap2]

I would just add a new series the same as Sales. Then remove the outlier value. Then plot a trend line on that. This will give you the chart and the trend line. Format the chart colors etc as you like.

For the percent difference you need the trend line equation. The line equation can be dislplayed by right click the trend line>Format Trendline>display equation. That can be displayed on your chart. as y = 37109x + 38484. Your x values will be 1,2,3,4,Nth. This will give you your y vaules. Now that you have the Y values for the trend line you can calcualte the percent difference.