Trend line polynomial

[stellanhakansson]

I am trying to reproduce y-values generated by the equation for a polynomial trendline with x-values 1-42. Y-values seem to be correct up to x=14 but as x increases beyond this value, y-values become increasingly incorrect.
I have checked my equation in detail but cannot see that it differs from that given by Excel. Something I missed? Greatful for advice.
BR, Stellan Håkansson

 

[rlv01]

I'd play around with it, but to do that I'd have to manually type in the equation from your graphic, and life is too short for that. You do have a minor error in the last term (51.862 vs. 51.682). You could also construct the equation in VBA as a UDF and use it for comparison. That said, your correlation coeff (R2) is only ~0.7 which is not a great fit, suggesting that you try other curve fits.

 

[KenU]

You may also consider getting the coefficients from Excel rather than typing them based on the trendline equation. The displayed equation will only show a level of precision as set in the options; but the calculated coefficients will be the full precision. Using Office 365, I use the formula shown below to calculate and apply the coefficients for a polynomial of a given order. I have replaced range references with pseudocode names. You should be able to use a range for X_Input and calculate the entire set of resulting Y values (it will spill as needed).

edit:
apparently formatting between code tags is a bad idea

=MMULT(X_Input^TRANSPOSE((order+1)-SEQUENCE(order+1))*LINEST(Y_Data,X_Data^TRANSPOSE(SEQUENCE(order))),SEQUENCE(order+1)/SEQUENCE(order+1))

Hope that helps.

Regards,
Ken

 

[KRice]

@KenU is spot on...this is a matter of coefficient precision. If you determine the coefficients to greater precision, this issue will disappear. The clue is that at higher x values, the x^5 and x^6 terms become very large, and your coefficients on those terms are very small, so any loss in coefficient precision means that your estimated values will differ substantially. Here is an example with the regression extracting higher precision (reg2...the red points, and coefficients shown above table). Generally, I would critically examine whether polynomial regression is appropriate. In some cases it is, but in other cases, it may not be.

Book1
	A	B	C	D
1		x6	5.00E-07	4.77E-07
2		x5	-7.00E-05	-7.25E-05
3		x4	4.30E-03	4.31E-03
4		x3	-1.25E-01	-1.25E-01
5		x2	1.80E+00	1.80E+00
6		x1	-1.20E+01	-1.20E+01
7		x0	5.19E+01	5.17E+01
8
9
10	Day	Observed (%)	reg1	reg2
11	1	44.44	41.49653	41.31322
12	2	20.00	34.04379	33.86104
13	3	37.50	28.89835	28.71596
14	4	37.93	25.54197	25.35902
15	5	15.15	23.53606	23.35006
16	6	25.00	22.51461	22.32028
17	7	14.71	22.17733	21.96507
18	8	30.77	22.28331	22.03718
19	9	13.21	22.64489	22.34029
20	10	17.65	23.122	22.72283
21	11	22.45	23.61681	23.07227
22	12	26.03	24.06875	23.30961
23	13	26.92	24.44989	23.38435
24	14	24.14	24.76069	23.26968
25	15	25.40	25.02606	22.95815
26	16	25.37	25.29189	22.45751
27	17	22.08	25.62179	21.78708
28	18	21.33	26.09435	20.97431
29	19	20.88	26.80061	20.05181
30	20	22.50	27.842	19.05458
31	21	11.11	29.32859	18.01775
32	22	11.11	31.37771	16.97449
33	23	11.69	34.11293	15.95445
34	24	16.84	37.66341	14.98237
35	25	16.90	42.16356	14.07711
36	26	11.97	47.75317	13.25109
37	27	18.00	54.57775	12.50993
38	28	11.11	62.78939	11.85254
39	29	10.00	72.54783	11.27154
40	30	13.46	84.022	10.75397
41	31	13.16	97.39187	10.28241
42	32	6.30	112.8507	9.836392
43	33	10.53	130.6075	9.394194
44	34	6.20	150.8901	8.93495
45	35	9.70	173.9486	8.441114
46	36	5.26	200.0584	7.901272
47	37	9.35	229.5252	7.31329
48	38	6.14	262.6884	6.687809
49	39	6.47	299.9266	6.052084
50	40	3.54	341.662	5.454161
51	41	8.46	388.3667	4.967409
52	42	3.15	440.5676	4.695382
Sheet2

Cell Formulas
Range	Formula
C11:D52	C11	=SUMPRODUCT($A11^{6;5;4;3;2;1;0},C$1:C$7)

 

[stellanhakansson]

You may also consider getting the coefficients from Excel rather than typing them based on the trendline equation. The displayed equation will only show a level of precision as set in the options; but the calculated coefficients will be the full precision. Using Office 365, I use the formula shown below to calculate and apply the coefficients for a polynomial of a given order. I have replaced range references with pseudocode names. You should be able to use a range for X_Input and calculate the entire set of resulting Y values (it will spill as needed).

edit:
apparently formatting between code tags is a bad idea

=MMULT(X_Input^TRANSPOSE((order+1)-SEQUENCE(order+1))*LINEST(Y_Data,X_Data^TRANSPOSE(SEQUENCE(order))),SEQUENCE(order+1)/SEQUENCE(order+1))

Hope that helps.

Regards,
Ken

Thanks, much appreciated. Best, /Stellan

 

[stellanhakansson]

@KenU is spot on...this is a matter of coefficient precision. If you determine the coefficients to greater precision, this issue will disappear. The clue is that at higher x values, the x^5 and x^6 terms become very large, and your coefficients on those terms are very small, so any loss in coefficient precision means that your estimated values will differ substantially. Here is an example with the regression extracting higher precision (reg2...the red points, and coefficients shown above table). Generally, I would critically examine whether polynomial regression is appropriate. In some cases it is, but in other cases, it may not be.

Book1
	A	B	C	D
1		x6	5.00E-07	4.77E-07
2		x5	-7.00E-05	-7.25E-05
3		x4	4.30E-03	4.31E-03
4		x3	-1.25E-01	-1.25E-01
5		x2	1.80E+00	1.80E+00
6		x1	-1.20E+01	-1.20E+01
7		x0	5.19E+01	5.17E+01
8
9
10	Day	Observed (%)	reg1	reg2
11	1	44.44	41.49653	41.31322
12	2	20.00	34.04379	33.86104
13	3	37.50	28.89835	28.71596
14	4	37.93	25.54197	25.35902
15	5	15.15	23.53606	23.35006
16	6	25.00	22.51461	22.32028
17	7	14.71	22.17733	21.96507
18	8	30.77	22.28331	22.03718
19	9	13.21	22.64489	22.34029
20	10	17.65	23.122	22.72283
21	11	22.45	23.61681	23.07227
22	12	26.03	24.06875	23.30961
23	13	26.92	24.44989	23.38435
24	14	24.14	24.76069	23.26968
25	15	25.40	25.02606	22.95815
26	16	25.37	25.29189	22.45751
27	17	22.08	25.62179	21.78708
28	18	21.33	26.09435	20.97431
29	19	20.88	26.80061	20.05181
30	20	22.50	27.842	19.05458
31	21	11.11	29.32859	18.01775
32	22	11.11	31.37771	16.97449
33	23	11.69	34.11293	15.95445
34	24	16.84	37.66341	14.98237
35	25	16.90	42.16356	14.07711
36	26	11.97	47.75317	13.25109
37	27	18.00	54.57775	12.50993
38	28	11.11	62.78939	11.85254
39	29	10.00	72.54783	11.27154
40	30	13.46	84.022	10.75397
41	31	13.16	97.39187	10.28241
42	32	6.30	112.8507	9.836392
43	33	10.53	130.6075	9.394194
44	34	6.20	150.8901	8.93495
45	35	9.70	173.9486	8.441114
46	36	5.26	200.0584	7.901272
47	37	9.35	229.5252	7.31329
48	38	6.14	262.6884	6.687809
49	39	6.47	299.9266	6.052084
50	40	3.54	341.662	5.454161
51	41	8.46	388.3667	4.967409
52	42	3.15	440.5676	4.695382
Sheet2

Cell Formulas
Range	Formula
C11:D52	C11	=SUMPRODUCT($A11^{6;5;4;3;2;1;0},C$1:C$7)

View attachment 67306

Many hanks. Problem solved. Learned a lot! Much grateful.
All the best, Stellan