Linear Regression Example in Excel

This example examines the problem of fitting a theoretical equation to experimental data in order to obtain the values of the different constants in the system. Detailed information about the problem can be found in Prickett et al. (2011) Elliott et al. (2007) Prickett et al. (2010) and Jochem and Kdrber (1987). Data provided courtesy of Dr. Richelle Prickett. 

Consider the problem of obtaining the values of the parameters in a theoretical equation that describes the osmotic pressure of the sodium chloride (NaCl) salt and hydroxyethyl starch (HES, chemical formula (C6HioOs) (C2H50) ). Based on the virial equation of state, it is assumed that the following equation can be used to describe the osmolality (77) of such a mixture  

Before linear regression can be applied, the above equation must be rearranged so that all known constant information is on the left-hand side and all the unknown variables are on the right-hand side. Thus, the equation would be rewritten as 

Entering all the data in the Excel spreadsheet gives the results shown in Figs. 8.28 and 8.29. 

Using the original data shows that the second point (77 = 1,314) is potentially an outlier, since its residual is extremely large. Thus, the row corresponding to this point (row 4 in the original layout) was deleted and the regression analysis was redone. The results are shown in Figs. 8.30 and 8.31. The results are much better as there are now no clear outliers and the data confidence intervals, especially for C3, are much smaller. 

---