Databases Reference
In-Depth Information
FIGURE 4.34
Using the TREND() Worksheet Function.
New_X (optional) are new
x
-values for which the TREND function should return corresponding
y
-values. If New_X is omitted, it is assumed to be the same as Known_X. If Const is TRUE or
omitted,
b
is calculated normally. If const is FALSE,
b
is set equal to 0 (zero), and the
m
-values
are adjusted so that .
Figure 4.34 shows how the LINEST function is set up within a Worksheet. Notice the green
box around the powers of
x,
and the blue box around the vector (column) of
y
solutions for a given
series of
x
. The TREND function syntax can be seen in the formula editor in Excel. The fitted
curve of solutions to the given polynomial is returned in column F.
With an understanding of how the mechanism works to accomplish polynomial curve fitting in
Excel, a tool can now be constructed to automate the process using the GUI shown in Figure 4.32. The
first step in this process would be to generate a Worksheet in such a format that the LINEST() and
TREND() functions can calculate the regression parameters and return a fitted curve (such as that shown
in Figure 4.35).
The code that will create a neatly formatted sheet as shown in Figure 4.35 follows. Note that
the degree of the polynomial to be fitted (
PolyDeg
) is a passed parameter to the subroutine in
which this code resides, as are
XRange
and
YRange
.
ymxmx
=+ ++
1
2
mx
n
n
2
'Create Polynomial Fitting Sheet
PolySheet$ = CreateSheet(ActiveWorkbook.Name, "PolyFit")
FIGURE 4.35
An example Worksheet for using TREND() and LINEST() Functions.
