Databases Reference
In-Depth Information
TABLE 4.3
Linear Regression Sample Data Set
X
Y
1
0.37
2
2.87
3
2.3
4
3.2
5
5.11
6
5.42
7
7.33
8
8.27
9
9.79
10
9.18
For this example, a sample data set must be constructed. This is a relatively easy task to
accomplish. Here, a nearly linear line will be constructed, with some degree of error intentionally
introduced. The data set constructed for this sample is shown in Table 4.3.
Here the X values are simply created using 1 to 10. The corresponding Y value is equal to the
X value
±
a value from 0 to 1. This is accomplished rather easily using the formula
=
A2
+
RANDBETWEEN(
−
100,100)/100
where A2 is really A[row] ({i.e., for row 3
A3}).
Excel has a tool for accomplishing linear regression in a relatively painless manner with the
Analysis Toolpak. In order to utilize the regression tool, the analysis toolpak must be installed. To
do so, choose from the menu
T
ools->Add-
I
ns and make sure the Analysis Toolpak and Analysis
Toolpak VBA is checked as shown in Figure 4.22.
The Analysis Toolpak not only has tools built into Excel, but contains VBA routines that can
be utilized to execute all the mechanisms built into the Analysis Toolpak. Unfortunately, use of the
Analysis Toolpack VBA routines is not very well documented. The macro recorder, however, can
=
FIGURE 4.22
Enabling use of the Analysis Toolpak.
