Using a Trendline Equation

[CAPIP1998]

I have made a scatterplot of employee attrition, and I've also created (using excel) a 4th degree polynomial trendline that best matches the data. Here's the equation:

y = -1E-07x4 + 2E-05x3 - 0.0006x2 + 0.0086x

I then put this formula into cells in order to plot where I fall on the trenline for a given timeframe in my attrition model (e.g. 1 month, 2 months, etc...). I used this cell formula:

=-1*(10^-7)*C2^4 + 2*(10^-5)*C2^3 - 0.0006*C2^2 + 0.0086*C2

The problem I'm getting is that when I try to graph my calculated cells, I get a totally different graph from my original trendline. I don't know what I'm missing here, please help if you can. I'll post the scatterploy and calculated data below, let me know if any further information is needed. Thanks.

Scatterplot Data
1 0.011902037
2 0.014648661
3 0.02059968
4 0.027466239
5 0.034332799
6 0.041199359
7 0.056076905
8 0.040283818
9 0.041199359
10 0.029755093
11 0.035248341
12 0.057679103
13 0.04165713
14 0.019226368
15 0.030899519
16 0.029297322
17 0.027237354
18 0.008239872
19 0.004348821
20 0.009155413
21 0.009613184
22 0.045319295
23 0.0421149
24 0.027466239
25 0.005722133
26 0.053559167
27 0.012359808
28 0.012817578
29 0.006637675
30 0.02059968
31 0.021286336
32 0.007324331
33 0.015106432
34 0.023346304
35 0.008010986
36 0.008239872
37 0.008468757
38 0
39 0.008926528
40 0
41 0.009384298
42 0.009613184
43 0.009842069
44 0
45 0.01029984
46 0
47 0.021515221
48 0.021972992


Calculated using formula
C2 C3
1 0.0080199
2 0.0149584
3 0.0209319
4 0.0260544
5 0.0304375
6 0.0341904
7 0.0374199
8 0.0402304
9 0.0427239
10 0.045
11 0.0471559
12 0.0492864
13 0.0514839
14 0.0538384
15 0.0564375
16 0.0593664
17 0.0627079
18 0.0665424
19 0.0709479
20 0.076
21 0.0817719
22 0.0883344
23 0.0957559
24 0.1041024
25 0.1134375
26 0.1238224
27 0.1353159
28 0.1479744
29 0.1618519
30 0.177
31 0.1934679
32 0.2113024
33 0.2305479
34 0.2512464
35 0.2734375
36 0.2971584
37 0.3224439
38 0.3493264
39 0.3778359
40 0.408
41 0.4398439
42 0.4733904
43 0.5086599
44 0.5456704
45 0.5844375
46 0.6249744
47 0.6672919
48 0.7113984

 

[pgc01]

Hi

You have data with 9 significant digits and are using coefficients in you equation with 1 or 2 significant digits! This means that you cannot trust the values your equation calculates.

I calculated in the worksheet coefficients for the equation and got (displaying with 10 significant digits), setting the intercept to 0 as I believe you also did:

y=-1.2220068595E-07x4+1.5005845382E-05x3-6.1321461526E-04x2+8.5906939661E-03x

With these equation you'll get a much better result.

Remark:

You can get the coefficients directly in the worksheet using Linest().

Check:

http://spreadsheetpage.com/index.php/tip/chart_trendline_formulas/

- in the chart, to see more digits, right-click on the trendline equation box and change the format of the numbers (for ex. scientific notation with 10 decimals). I believe, however, that in the case of the intercept set to zero, as you did, it's better to use Linest() in the worksheet (I can't find the article that discusses this case, I believe it's not for all versions of excel and I may be wrong)

Using X in column A and Y in column B, I used the formula:

=LINEST(B2:B49,A2:A49^{1,2,3,4},FALSE)

to get the coefficients.

Although your data is very irregular, you'll get much smaller errors, especially for big values of X.

HTH

 

[FrigateSkimmer]

PGC,

I had also encountered the same problem, and your solution is most welcome.

Too bad MS didn't think to put a "Number" tab on the "Add Trendline" pop-up menu. It would have been obvious to the user that the formula needed some additional attention...

FS