Trend formula completely wrong

B

Beleriand_K

New Member
Joined
Jul 3, 2019
Messages
8
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.




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

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
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.
 
Last edited:
Upvote 0
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.
Click to expand...


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 :)
 
Upvote 0
If you changed to an xy scatter it should be plotting 80 as 80 not simply as point 1.
 
Upvote 0
RoryA said:
If you changed to an xy scatter it should be plotting 80 as 80 not simply as point 1.
Click to expand...

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.
 
Upvote 0
If you post your data, I show you how to do the regression.
 
Upvote 0
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 . . .
 
Last edited:
Upvote 0
shg said:
If you post your data, I show you how to do the regression.
Click to expand...

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 . . .
Click to expand...


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.
 
Upvote 0
Missed that it was in your post.

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

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


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 :)
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top