Databases Reference
In-Depth Information
FIGURE 4.32
Polynomial regression tool GUI.
Table 4.4 shows how a spreadsheet must be set up to utilize the LINEST function to calculate
the coefficients for a third-degree polynomial fit. The first column must contain the original
x
data.
The subsequent columns must have
x
raised to the power
n
up to the
n
th power or degree of the
polynomial to be fit. The last column will contain the
y
data.
The LINEST function calculates the statistics for a line by using the “least squares” method
to calculate a straight line that best fits the given data and returns an array that describes the line.
Because this function returns an array of values, it must be entered as an
array formula
.
The equation for the line is:
ymxb
=+
(linear case)
(4.13)
2
ymxmx
=+ ++
1
...
b
(polynomial case)
(4.14)
2
TABLE 4.4
LINEST Function Spreadsheet Format
X 2 3 4
Y
adj
Y
adj
1
1
1
1
0
0
0
2
4
8
16
3
0.3
3.3
3
9
27
81
2
0.2
1.8
4 6
4
256
9
0.6
8.4
5 5 125
625
36
3.3
39.3
6 6 216
1296
85
6.4
78.6
7 9 343
2401
162
2.6
159.4
8 4 512
4096
273
14.2
287.2
9 1 729
6561
424
37.4
386.6
10
100
1000
10000
621
48.4
669.4
