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)
   315   316   317   318   319   320   321   322   323   324   325