LI NEST

Of course, to plot a calculated curve you need to have an equation that fits the data. It may be the least-squcires straight line (obtained from LI NEST) that best fits the data, or a curve produced by an equation appropriate for the data. 

To illustrate the procedure, the method is applied to a small data set shown in Figure 12-8. The formula Y = 5 X + 3 + 0.1 (0.5-RAND()) was used to generate Y(data) in column B, with a small amount of experimental "noise" provided by the RAND() function. A linear function was chosen to permit comparison of the standard deviations returned by LI NEST with those provided by this method. Because the test equation is a simple linear one, the 5F/5aj values 8y/8ffi and 8y/8 in columns H and 1 of Figure 12-8) are very close to simple integer values. This is not the case when the procedure is applied to more complicated functions. 

Compare the standard deviations using the forgoing procedure (cells L24 and M24 in Figure 12-9) with those obtained from LI NEST, which are shown in row 32 of Figure 12-10. Once again, it should be made clear that a linear problem was chosen to permit comparison between the standard deviations of the regression coefficients and those from LINEST,... 

Figure 12-10. Comparison of regression statistics returned by LI NEST.

If you use a worksheet function within VBA that returns an array, the lower array index will be 1. Such worksheet functions include LI NEST, TRANSPOSE, MINVERSE, MMULT. Other functions that return arrays include the VBA function Caller when used with a menu command or toolbutton. 

Figure 17-4. LI NEST fails when used with a non-adjacent selection of known x s...

Alternatively the end-point can be obtained algebraically. LI NEST was used to obtain the slope and intercept of the straight-line portion of the data, shown in Figure 20-7 (the last five rows of data points were not included). The intercept is 44.44 and the slope is -1.35. 

One way to handle a curved calibration line is to fit the line to a power series. A cubic equation (y = a + bx + cx + dx ) is usually sufficient to fit a case such as Figure 21-1. (In any event, since there are only six known points, you couldn t use a polynomial with more than five adjustable parameters.) You can use either LI NEST or the Solver to obtain the coefficients of the power series. Figure 21-2 shows a spreadsheet in which LI NEST is used to find the regression coefficients for the equation Rdg = a + bx ppm + c x (ppm) + dx (ppm) ... 

Applying LI NEST to the data in the straight-line portion of the slow process (rows 17-26 of Figure 23-6) yields the rate constant for the slow process and permits the calculation of A oo from the intercept value (Aq for the slow process is Aoo for the fast process). From In (Aoo - Ag) = -1.315, Aoo - Ag = 0.269, from which A oo = Ag = 0.439, as shown in Figure 23-7. 

LI NEST Returns the parameters of multiple linear regression. 

This is the correlation coefficient returned by LI NEST. For more details, see LINEST. 

Oxygen.xls illustrates the use of the LI NEST function to perform multiple linear regression. 

Of greatest use, although not so easy to use, is LI NEST which in the command line of Excel has the form =LINEST(y-range, x-range,... 

Hence the output from LI NEST gives the same values as the first principle analysis in example 5.1, but it takes far less time to implement. 

Note that an alternative to inputting the =LINEST function in the command line is to select your 2x5 array of cells for the output, then to use the function option (from the Insert menu, or toolbar icon fx) and select LI NEST. A dialogue box similar to that shown in spreadsheet 5.5 appears and you can enter the ranges for y and x and the constant and stats in the appropriate spaces. You still need to press Ctrl-Shift-Enter to see all the output in the 5x2 array. 

This is the value in the LI NEST table. The associated probability may be calculated from =FDIST(F,1,df). However, as explained above, it would be a terrible thing if our calibration did not lead to a significant F, as we know that the model does fit the data well (or we would not be using it for calibration ). 

If we need only one of the LI NEST outputs in a calculation, it may be extracted by the function =INDEX(array, row, column), without having to display the entire array. For example, the standard error of the regression is =INDEX(LINEST(y-range, x-range, const, stats),3,2) because sy/x is in the third row, second column of LI NEST. 

A common application of LI NEST is to find the best straight line through a set of data points, i.e., to find the regression parameters m and b of the best straight line y = mx + b through the data points. 

Plotting 1/y vs 1 fx will yield a straight line with slope 1 fab and intercept 1 /a. LI NEST can be used to provide the regression coefficients l/ab and 1 /a, and their associated standard deviations. The coefficients a and b can be obtained from the regression coefficients (a = 1/intercept, b = intercept/slope). However, relationships dealing with the propagation of error must be used to calculate the... 

---