Page 320 - Excel for Scientists and Engineers: Numerical Methods
P. 320
CHAPTER 13 LINEAR REGRESSION AND CURVE FITTING 297
Handling Noncontiguous Ranges
of known-x's in LINEST
One of the few limitations of LINEST is that the range of known-x's must be
a contiguous selection (e.g., $A$2:$C$13 in Figure 13-9). Occasionally, you may
wish to perform multiple linear regression where the known-x's are not in
adjacent rows, and it may not be convenient to rearrange the spreadsheet so as to
obtain a contiguous range of known-x's. You can use the custom function Arr to
combine separate ranges into a single array. For example, if the ranges of
independent variables xl, x2 and x3 were in the ranges A2:A13, C2:C13 and
E2:E13, respectively, and the dependent variable y in F2:F13, the LINEST
expression would be
=LINEST( F2: F13, Arr(A2:Al3, C2:C13, E2: El 3), 1 ,I )
A LINEST Shortcut
Here's a shortcut that eliminates the need to create the columns of W2 and @
in Figure 13-10. If you've read Chapter 4, "Number Series," and understand
array constants, you'll understand how the formula
{=LINEST(D2:D14,A2:A14"{1,2,3},1 ,I)}
creates an array of the values of the independent variable W raised to the first,
second and third powers. Unlike the braces that are automatically placed around
an array formula when you enter it by using CONTROL+SHIFT+ENTER, you must
type the braces around the values of the array constant.
You can examine that part of the formula by highlighting M:A14"{1,2,3} in
the formula bar and pressing F9; you'll see the result displayed in the formula bar
(only a portion of it is shown here):
{0,0,0;5,25,125;10,100,1000;15,225,3375;20,400,8000; ...}
Note that successive array elements in a row are separated by commas, and
rows of elements are separated by semicolons.
The formula, which must be entered by using CONTROL+SHIFT+ENTER,
returns the same values that are shown in Figure 13-10.
LINEST's Regression Statistics
Additional regression statistics are returned by LINEST in rows 3, 4 and 5 of
the array. The mathematical relationships between the regression statistics are
given in equations 13-14 to 13-19 (N = number of data points, k = number of
regression coefficients to be determined):
df(degrees of freedom) = N- k (13-14)