**Microsoft Excel Spreadsheets for Fitting Sorption Data**

**Carl H. Bolster, USDA-ARS**

**The spreadsheets have been recently updated (3/20/2010). Click here for details. **

**Introduction:** Sorption models are commonly used for describing solute and metal sorption to soils. When fitting sorption models to sorption data, however, the user must be aware that certain statistical limitations exist with both linear and nonlinear versions of the models. Ongoing research at the Animal Waste Management Research Unit of the USDA-ARS addresses the effect of these statistical limitations on fitting phosphorus sorption data with various sorption models. Currently, the following studies have been completed:

**1. Problems associated with using linearized versions of the Langmuir equation: **Although linearized versions of the Langmuir equation are mathematically equivalent to the nonlinear equation, the transformation of data required for linearization can result in modifications of error structure, introduction of error into the independent variable, and alteration of the weight placed on each data point. This can often times lead to differences in fitted parameter values between linear and nonlinear versions of the Langmuir model. In addition, our research has shown that the use of linearized Langmuir equations needlessly limits the ability to accurately model sorption data with more sophisticated sorption models (C.H. Bolster and G.M. Hornberger. 2007. On the Use of Linearized Langmuir Equations. Soil Science Society of America Journal 71:1796-1806 see also the erratum). Because the use of linearized Langmuir equations is largely due to the ease of using linear regression, we make available an easy-to-use Microsoft Excel spreadsheet capable of fitting nonlinear sorption equations to isotherm data. The spreadsheet generates best-fit parameters, parameter uncertainties, parameter correlations, and goodness-of-fit measures. The accuracy of the spreadsheet has been thoroughly tested by comparing model fits and parameter estimates with a more sophisticated software package (SAS 9.2).To download the spreadsheet click here and save to desktop. NOTE â€“ due to our findings that weighting by the inverse of the variance in the dependent variable can lead to poorer parameter estimates and uncertainties than with unweighted least squares regression (see below), the weighting option has been removed from the spreadsheet (3/20/2010). If weighting of data is needed, please contact me at carl.bolster@ars.usda.gov.

**2. Addressing the errors-in-variables problem associated with fitting the Langmuir model to sorption data:** The Langmuir model is often fit to sorption data using least squares regression. An important assumption of least squares regression is that the predictor variable is error free. In the case of sorption data, however, this assumption is not valid because the equilibrium concentration - treated as the predictor variable in the Langmuir equation - is the variable usually measured; therefore the potential for parameter bias exists when obtaining parameter estimates through least squares regression. Although not commonly used, alternative regression methods do exist which either explicitly account for error in the predictor variable or significantly reduce the error in the predictor variable by modifying the Langmuir equation so that the true independent variable, initial concentration, is used as the predictor variable. In this study, the differences in fitted parameters and model fits between three different regression methods are explored by fitting P sorption data collected on 26 different soil samples. For a majority of soils tested in this study, the differences in model fits between the three regression methods were not statistically significant. Statistical differences, however, were observed in over a third of the soils suggesting that under some conditions errors in the predictor variable may be large enough to produce biased parameter estimates (C.H. Bolster 2008. Revisiting a Statistical Shortcoming When Fitting the Langmuir Model to Sorption Data. Journal of Environmental Quality 37:1986-1992). A spreadsheet with a modified version of the Langmuir equation which uses the initial concentration, rather than the equilibrium concentration, as the independent variable can be downloaded by clicking here and saving to your desktop. Please note, however, that the use of this equation can give the false impression that the Langmuir model is fitting the data adequately. After fitting, the data and model fits should be plotted as S vs C for inspection.

**3. Properly weighting sorption data for least-squares analysis:** In this study we examined the role of proper weighting in the least-squares (LS) analysis of phosphorus sorption data when both the dependent (*y*) and independent (*x*) variables contain heteroscedastic errors. We compared parameter estimates and uncertainties obtained with unweighted LS (ULS) regression with those obtained using two different weighted LS (WLS) regression methods. In the first WLS method we weighted the data by the inverse of the variance in *y*. In the second WLS method, we included the variance in *x* when calculating the weights. This method, commonly referred to as the effective variance method, has primarily been applied to data with uncorrelated errors in *x* and *y*, conditions not representative of sorption studies where* *values of *y* are calculated from measured values of *x*. Therefore, in this study we tested a modified version of the effective weighting function which specifically accounts for correlated errors in *x* and *y*. The accuracy of the different weighting methods was assessed using Monte Carlo (MC) simulations and high-replication sorption data obtained for three different soil types. Our findings show that the effective variance weighting method provides superior parameter estimates and uncertainties compared with ULS or traditional WLS methods, though the differences between the weighting methods are not always large enough to be of practical concern. We also find that weighting by the effective variance allows for improved assessments of model fits. (C.H. Bolster and J. Tellinghuisen. 2010. On the Significance of Properly Weighting Sorption Data for Least-Squares Analysis. Soil Science Society of America 74:670-679) Please contact me if you are interested in the spreadsheet used in this study.

Please send comments, questions, and suggestions to carl.bolster@ars.usda.gov.

Disclaimer: Although I have tested these spreadsheets against the output from SAS I cannot guarantee that the coding is error free. If you find what appears to be an error please contact me immediately. Please regularly check the Web site for updates and/or corrections.