Performing Regressions with LINEST

At this point, a considerable amount of theory on Hansch analysis has been presented with almost no examples of practice. The next three Case Studies will hopefully solidify ideas on Hansch analysis that have already been discussed. Each Case Study introduces a different idea. The first is an example of a very simple Hansch equation with a small data set. The second demonstrates the use of squared parameters in Hansch equations. The third and final Case Study shows how indicator variables are used in QSAR studies. If you are unfamiliar with performing linear regressions, be sure to read Appendix B on performing a regression analysis with the LINEST function in almost any common spreadsheet software. A section in the appendix describes in great detail how to derive Equations 12.20 through 12.22 in the first Case Study. 

The first step is to plot the data (figure 5.10) and then perform a regression analysis of the calibration data and the uncertainty in the calibration coefficients a and b. This is best performed using LINEST as shown in spreadsheet 5.6. LINEST indicates the values of a and b are 0.2274 and l.085mM respectively, with the standard deviations 0.0095 for sa and 0.042mM-1 for sb. 

---