Linear Regression Template

Requirements basic Excel installation plus ability to use macros. 

Goal perform linear regression in Excel in an easy and straightforward manner. Filename linearregresion.xltm 

Description a screenshot of the plain template is shown in Eig. 8.25. The yellow blocks are where the required data are entered. The green block represents the row in which an array formula needs to be entered. The complete green row 

Warning-, this template requires that the internal macros be enabled. Also, the array formulae need to be properly entered. 

---

The nonlinear regression Excel template used is set up identically to that of the linear regression template. The only difference is that now the estimated parameter values are not computed using a formula. Instead, they must be determined using Solver. Given the problem set-up, initial parameter estimates can be a bit of an issue, as the solution is sensitive to them. A recommended initial guess would be 0.5 for b and 2.5 for B. The macros are shown in Sect. 8.7.2.3 VB Macros. 

Figure 39-15 Real-time quantitative PCR of 6CR-A6L transcripts. Serial log dilutions of the plasmids containing the BCR-ABL transcripts. Real-time PCR provides a remaricable dynamic range of quantification (10 to 10° template copies) in this example.The upper panel shows the amplification curves obtained using the BCR-ABL containing plasmids and an unknown patient sample depicted as a dotted line. Note the regular -3.3-cyde interval between log dilutions.The lower panel shows a standard curve linear regression of cycle number versus log template concentration). As indicated by the dotted tine in the lower panel, the cycle threshold (onset of the log-linear phase of the amplification curve) can be used to quantitate the copy number of the target in the tested sample. (See Color Plate 4.)...

The efficiency (E) of a PCR assay is calculated by the gradient (m) of a standard curve. For that purpose cDNA dilutions (e.g. 100%, 10%, 1%, 0.1%) are used as templates for the graphical structure. A linear regression line through the curve has the gradient -m (when plotted with increasing DNA concentration) ... 

Description a screenshot of the plain template is shown in Fig. 8.26. The yellow blocks are where the required data are entered. Note that Solver needs to be used to obtain a solution to the problem. The configuration of Solver is shown as an inset in Fig. 8.26. The layout and formatting of the results are similar to the linear regression case. Two important differences are that the model and its Jacobian must be entered as a macro and that Solver must be used. The spreadsheet automatically creates the normal probability plot for the residuals and plots of the residuals as a function of y and y, as well as a time series plot of the residuals. Additional plots can be created by the user. An example of how to use the template is provided in Sect. 8.7.2 Nonlinear Regression Example. 

This section presents three examples that show how to implement various forms of regression analysis in Excel. The topics considered are linear regression, nonlinear regression, and analysis of factorial design. All examples are based on real data obtained from experiments. The procedures use the appropriate templates for... 

Hernandez, A., and M. T. Ruiz. 1998. An Excel Template for Calculation of Enzyme Kinetic Parameters by Non-Linear Regression. Bioinformatics 14 (2) 227-228. 

Figure 2 Adsorption isotherm of methyl orange dye on a silica gel templated with methyl orange [9]. The linear least-square regression to the data ( ) represents a Freundlich isotherm.

Another approach is to use a nonlinear regression technique, which produces a weighted least square that maximizes the efficiency of the parameter estimation. This technique does not require linearization and can be used to determine multiparameters. Hernandez and Ruiz (1998) developed an Excel template for the calculation of enzyme kinetic parameters using this technique. 

---