Trend formula completely wrong

Beleriand_K · Jul 3, 2019

When I enter the following data into Excel and generates af Forecast Analysis with a Polynomial Trendline (Order 2) Excel shows an almost perfect trendline in the diagram. But when I choose Display equation on chart, it comes up with a Trend formula that has nothing to do with reality.

This is Excels Trendline formula:

Y = -0,0044x^2+0,6225x+13,408

If I put three of the numbers above into this formula, the results are this:

80 => 35,048 (real result in table: 13,88)
100 => 31,658 (real result in table: 24,53)
120 => 24,748 (real result in table: 31,63)

Apart from being very different from the results in the table, the Y-values gets smaller in the Trend formula, while they get bigger in the input table. So there seems to something fundamentally wrong with the equation.

I have searched the internet for an explanation, but without succes. The only answer I have found is, that I should use more decimals, but I have tried to extend the number of decimals to 30, and it doesn't help.

I suppose Excel is working as it should, so it must be me doing something wrong. Can anyone figure out, what it is? Because I surely can't.

80	13,88
81	14,54
82	15,18
83	15,80
84	16,41
85	17,01
86	17,59
87	18,16
88	18,72
89	19,29
90	19,79
91	20,31
92	20,82
93	21,32
94	21,81
95	22,29
96	22,75
97	23,21
98	23,66
99	24,10
100	24,53
101	24,95
102	25,36
103	25,77
104	26,17
105	26,56
106	26,94
107	27,31
108	24,68
109	28,04
110	28,40
111	28,75
112	29,09
113	29,43
114	29,76
115	30,08
116	30,40
117	30,72
118	31,03
119	31,33
120	31,63

shg · Jul 3, 2019

Chances are you have a line chart, which regresses across categories {1,2,3,...}

Change to x-y scatter.

EDIT: If you want the actual coefficients to use elsewhere, use LINEST:

{=LINEST(y, x^{2,1})}

That will show the coefficients of x^2, x, and the constant term, in that order.

Beleriand_K · Jul 3, 2019

shg said:
Chances are you have a line chart, which regresses across categories {1,2,3,...}

Change to x-y scatter.

EDIT: If you want the actual coefficients to use elsewhere, use LINEST:

{=LINEST(y, x^{2,1})}

That will show the coefficients of x^2, x, and the constant term, in that order.

Changing to x-y scatter didn't change the formula, but it changed the diagram to something, that made me realise how Excel works with these numbers. Apparently Excel doesn't read 80 as the number 80, but as 0 (being the first number and therefore the first x-coordinate in the diagram). So to make the formula useful, I just have to subtract 80 from any y-value I enter into the formula.

It's a bit strange that Excel won't work with the numbers I supply, but knowing this makes it possible to move on with a formula that with a slight twist becomes useful.

Thank you for your assistance

RoryA · Jul 3, 2019

If you changed to an xy scatter it should be plotting 80 as 80 not simply as point 1.

Beleriand_K · Jul 3, 2019

RoryA said:
If you changed to an xy scatter it should be plotting 80 as 80 not simply as point 1.

It didn't. It changed the x-axis to start with 0 instead of 80, as it was before I changed to xy-scatter. I'm not used to working with this part of Excel, so I wasn't aware that wasn't supposed to happen.

shg · Jul 3, 2019

If you post your data, I show you how to do the regression.

Repush · Jul 3, 2019

your data (taken from first post) results in:
y = -0,0041x^2 + 0,6034x + 13,531 (line plot)
y = -0,0041x^2 + 1,245x - 59,478 (scatter)
so it does change . . .

Beleriand_K · Jul 3, 2019

shg said:
If you post your data, I show you how to do the regression.

I posted the data in the opening post. I would have enclosed a piece of the spreadsheet, but I couldn't figure out how to do that.

Repush said:
your data (taken from first post) results in:
y = -0,0041x^2 + 0,6034x + 13,531 (line plot)
y = -0,0041x^2 + 1,245x - 59,478 (scatter)
so it does change . . .

In my spreadsheet strange things happen, when I change to xy-scatter, but a change in the formula isn't one of them. I would have liked to enclose a screenshot, but apparently it requires an url-adress to upload an image.

shg · Jul 3, 2019

Missed that it was in your post.

	A	B	C	D
1	x^2	x	b
2	-0.00406	1.244968	-59.4783	A2:C6: {=LINEST(B9:B49, A9:A49^{1,2},,TRUE)}
3	0.000613	0.12268	6.074818
4	0.991645	0.4907	#N/A
5	2254.965	38	#N/A
6	1085.929	9.149873	#N/A
7
8	x	y	Fit
9	80	13.88	14.13	C9: =SERIESSUM(A9, 2, -1, $A$2:$B$2) + $C$2
10	81	14.54	14.72
11	82	15.18	15.31
12	83	15.80	15.88
13	84	16.41	16.45
14	85	17.01	17.01
15	86	17.59	17.56
16	87	18.16	18.10
17	88	18.72	18.63
18	89	19.29	19.16
19	90	19.79	19.68
20	91	20.31	20.19
21	92	20.82	20.69
22	93	21.32	21.18
23	94	21.81	21.67
24	95	22.29	22.15
25	96	22.75	22.62
26	97	23.21	23.08
27	98	23.66	23.53
28	99	24.10	23.97
29	100	24.53	24.41
30	101	24.95	24.84
31	102	25.36	25.26
32	103	25.77	25.67
33	104	26.17	26.08
34	105	26.56	26.47
35	106	26.94	26.86
36	107	27.31	27.24
37	108	24.68	27.61
38	109	28.04	27.98
39	110	28.40	28.33
40	111	28.75	28.68
41	112	29.09	29.02
42	113	29.43	29.35
43	114	29.76	29.68
44	115	30.08	29.99
45	116	30.40	30.30
46	117	30.72	30.60
47	118	31.03	30.89
48	119	31.33	31.17
49	120	31.63	31.44

Beleriand_K · Jul 3, 2019

shg said:
Missed that it was in your post.

A
B
C
D
1
x^2
x
b
2
-0.00406
1.244968
-59.4783
A2:C6: {=LINEST(B9:B49, A9:A49^{1,2},,TRUE)}
3
0.000613
0.12268
6.074818
4
0.991645
0.4907
#N/A
5
2254.965
38
#N/A
6
1085.929
9.149873
#N/A
7
8
x
y
Fit
9
80
13.88
14.13
C9: =SERIESSUM(A9, 2, -1, $A$2:$B$2) + $C$2
10
81
14.54
14.72
11
82
15.18
15.31
12
83
15.80
15.88
13
84
16.41
16.45
14
85
17.01
17.01
15
86
17.59
17.56
16
87
18.16
18.10
17
88
18.72
18.63
18
89
19.29
19.16
19
90
19.79
19.68
20
91
20.31
20.19
21
92
20.82
20.69
22
93
21.32
21.18
23
94
21.81
21.67
24
95
22.29
22.15
25
96
22.75
22.62
26
97
23.21
23.08
27
98
23.66
23.53
28
99
24.10
23.97
29
100
24.53
24.41
30
101
24.95
24.84
31
102
25.36
25.26
32
103
25.77
25.67
33
104
26.17
26.08
34
105
26.56
26.47
35
106
26.94
26.86
36
107
27.31
27.24
37
108
24.68
27.61
38
109
28.04
27.98
39
110
28.40
28.33
40
111
28.75
28.68
41
112
29.09
29.02
42
113
29.43
29.35
43
114
29.76
29.68
44
115
30.08
29.99
45
116
30.40
30.30
46
117
30.72
30.60
47
118
31.03
30.89
48
119
31.33
31.17
49
120
31.63
31.44

<tbody>
</tbody>

Very interesting! I have worked with arrays before, but never LINEST. It seems that the values are almost perfect compared with the real values. This must be the way to do it. Thank you again

Trend formula completely wrong

Beleriand_K

New Member

Excel Facts

shg

MrExcel MVP

Beleriand_K

New Member

RoryA

MrExcel MVP, Moderator

Beleriand_K

New Member

shg

MrExcel MVP

Repush

Board Regular

Beleriand_K

New Member

shg

MrExcel MVP

Beleriand_K

New Member

Forum statistics

Share this page

Trend formula completely wrong

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP

Board Regular

New Member

MrExcel MVP

New Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock