Abstract
We describe an intuitive and rapid procedure for analyzing experimental data by nonlinear least-squares fitting (NLSF) in the most widely used spreadsheet program. Experimental data in x/y form and data calculated from a regression equation are inputted and plotted in a Microsoft Excel worksheet, and the sum of squared residuals is computed and minimized using the Solver add-in to obtain the set of parameter values that best describes the experimental data. The confidence of best-fit values is then visualized and assessed in a generally applicable and easily comprehensible way. Every user familiar with the most basic functions of Excel will be able to implement this protocol, without previous experience in data fitting or programming and without additional costs for specialist software. The application of this tool is exemplified using the well-known Michaelis–Menten equation characterizing simple enzyme kinetics. Only slight modifications are required to adapt the protocol to virtually any other kind of dataset or regression equation. The entire protocol takes ∼1 h.
This is a preview of subscription content, access via your institution
Access options
Subscribe to this journal
Receive 12 print issues and online access
$259.00 per year
only $21.58 per issue
Buy this article
- Purchase on SpringerLink
- Instant access to full article PDF
Prices may be subject to local taxes which are calculated during checkout
Similar content being viewed by others
References
De Levie, R. Advanced Excel for Scientific Data Analysis 2nd edn. (Oxford University Press, New York, 2008).
Johnson, M.L. Why, when, and how biochemists should use least squares. Anal. Biochem. 206, 215–225 (1992).
Press, W.H., Teukolsky, A.S., Vetterling, W.T. & Flannery, B.P. Modeling of data. In Numerical Recipes in C: The Art of Scientific Computing 2nd edn. 656–706 (Cambridge University Press, New York, 1992).
Bevington, P.R. & Robinson, D.K. Least-squares fit to an arbitrary function. In Data Reduction and Error Analysis for the Physical Sciences 3rd edn. 142–167 (McGraw-Hill Higher Education, New York, 2009).
Motulsky, H. & Christopoulos, A. Fitting Models to Biological Data Using Linear and Nonlinear Regression: A Practical Guide to Curve Fitting 2nd edn. (GraphPad Software, San Diego, 2003).
Orvis, W.J. Excel for Scientists and Engineers 1st edn. (SYBEX, Alameda, 1995).
Fylstra, D., Lasdon, L., Watson, J. & Waren, A. Design and use of the Microsoft Excel Solver. Interfaces 28, 29–55 (1998).
Lasdon, L.S., Waren, A.D., Jain, A. & Ratner, M. Design and testing of a generalized reduced gradient code for nonlinear programming. ACM T. Math. Software 4, 34–50 (1987).
Beck, A., Tsamaloukas, A.D., Jurcevic, P. & Heerklotz, H. Additive action of two or more solutes on lipid membranes. Langmuir 24, 8833–8840 (2008).
Tsamaloukas, A.D., Beck, A. & Heerklotz, H. Modeling the micellization behavior of mixed and pure n-alkyl-maltosides. Langmuir 25, 4393–4401 (2009).
Plouffe, L. Jr. & Luxenberg, S.N. Biological modeling on a microcomputer using standard spreadsheet and equation solver programs: the hypothalamic-pituitary-ovarian axis as an example. Comput. Biomed. Res. 25, 117–130 (1992).
Hargrove, J.L., Heinz, G. & Heinz, O. Modeling transitions in body composition: the approach to steady state for anthropometric measures and physiological functions in the Minnesota human starvation study. Dyn. Med. 7, 16 (2008).
Stevens, P.W. & Kelso, D.M. Estimation of the protein-binding capacity of microplate wells using sequential ELISAs. J. Immunol. Methods 178, 59–70 (1995).
Kawamata, W. & Toyoshima, H. Estimation of T1 and T2 using general-purpose spreadsheet software. Nippon Hoshasen Gijutsu Gakkai Zasshi 65, 306–311 (2009).
Rohatagi, S., Hochhaus, G., Möllmann, H., Barth, J. & Derendorf, H. Pharmacokinetic interaction between endogenous cortisol and exogenous corticosteroids. Pharmazie 50, 610–613 (1995).
Dansirikul, C., Choi, M. & Duffull, S.B. Estimation of pharmacokinetic parameters from non-compartmental variables using Microsoft Excel. Comput. Biol. Med. 35, 389–403 (2005).
Meineke, I. & Brockmöller, J. Simulation of complex pharmacokinetic models in Microsoft Excel. Comput. Methods Programs Biomed. 88, 239–245 (2007).
Briones, A.M. Jr. & Reichardt, W. Estimating microbial population counts by 'most probable number' using Microsoft Excel. J. Microbiol. Methods 35, 157–161 (1999).
Sonnenberg, A. Special review: game theory to analyse management options in gastro-oesophageal reflux disease. Aliment. Pharmacol. Ther. 14, 1411–1417 (2000).
Ward, R., Schlenker, J. & Anderson, G.S. Simple method for developing percentile growth curves for height and weight. Am. J. Phys. Anthropol. 116, 246–250 (2001).
Zhang, F. & Roush, W.B. Multiple-objective (goal) programming model for feed formulation: an example for reducing nutrient variation. Poult. Sci. 81, 182–192 (2002).
Guevara, V.R. Use of nonlinear programming to optimize performance response to energy density in broiler feed formulation. Poult. Sci. 83, 147–151 (2004).
Kuo, P.C., Schroeder, R.A., Mahaffey, S. & Bollinger, R.R. Optimization of operating room allocation using linear programming techniques. J. Am. Coll. Surg. 197, 889–895 (2003).
Maurer, M., Kühleitner, M., Gasser, B. & Mattanovich, D. Versatile modeling and optimization of fed batch processes for the production of secreted heterologous proteins with Pichia pastoris . Microb. Cell Fact. 5, 37 (2006).
Abdel-Fattah, Y.R. et al. Application of factorial experimental designs for optimization of cyclosporin A production by Tolypocladium inflatum in submerged culture. J. Mol. Microbiol. Biotechnol. 17, 1930–1936 (2007).
Burke, J.A. Two mathematical programming models of cheese manufacture. J. Dairy Sci. 89, 799–809 (2006).
Schrader, H. & Svec, A. Comparison of ionization chamber efficiencies for activity measurements. Appl. Radiat. Isot. 60, 369–378 (2004).
Brown, A.M. A step-by-step guide to non-linear regression analysis of experimental data using a Microsoft Excel spreadsheet. Comput. Methods Programs Biomed. 65, 191–200 (2001).
Brown, A.M. A non-linear regression analysis program for describing electrophysiological data with multiple functions using Microsoft Excel. Comput. Methods Programs Biomed. 82, 51–57 (2006).
Branco, T.J., Botelho do Rego, A.M., Ferreira, M.I. & Vieira Ferreira, L.F. Luminescence lifetime distributions analysis in heterogeneous systems by the use of Excel's Solver. J. Phys. Chem. B 109, 15958–15967 (2005).
Li, J. Comparison of the capability of peak functions in describing real chromatographic peaks. J. Chromatogr. A 952, 63–70 (2002).
Nikitas, P., Pappa-Louisi, A. & Papageorgiou, A. On the equations describing chromatographic peaks and the problem of the deconvolution of overlapped peaks. J. Chromatogr. A 912, 13–29 (2001).
Nikitas, P., Pappa-Louisi, A., Papageorgiou, A. & Zitrou, A. On the use of genetic algorithms for response surface modeling in high-performance liquid chromatography and their combination with the Microsoft Solver. J. Chromatogr. A 942, 93–105 (2002).
Karmarkar, S., Garber, R., Kluza, J. & Koberda, M. Gel permeation chromatography of dextrans in parenteral solutions: calibration procedure development and method validation. J. Pharm. Biomed. Anal. 41, 1260–1267 (2006).
Dasgupta, P.K. Chromatographic peak resolution using Microsoft Excel Solver. The merit of time shifting input arrays. J. Chromatogr. A 1213, 50–55 (2008).
van Dijk, J.W. Thermoluminescence glow curve deconvolution and its statistical analysis using the flexibility of spreadsheet programs. Radiat. Prot. Dosimetry 119, 332–338 (2006).
Walsh, S. & Diamond, D. Non-linear curve fitting using Microsoft Excel Solver. Talanta 42, 561–572 (1995).
Kane, P. & Diamond, D. Determination of ion-selective electrode characteristics by non-linear curve fitting. Talanta 44, 1847–1858 (1997).
Luther, G.W. III, Theberge, S.M. & Rickard, D. Determination of stability constants for metal-ligand complexes using the voltammetric oxidation wave of the anion/ligand and the DeFord and Hume formalism. Talanta 51, 11–20 (2000).
Comuzzi, C., Polese, P., Melchior, A., Portanova, R. & Tolazzi, M. SOLVERSTAT: a new utility for multipurpose analysis. An application to the investigation of dioxygenated Co(II) complex formation in dimethylsulfoxide solution. Talanta 59, 67–80 (2003).
Safavi, A., Maleki, N., Rostamzadeh, A. & Maesum, S. CCD camera full range pH sensor array. Talanta 71, 498–501 (2007).
Parsons, J.D. A high-throughput method for fitting dose–response curves using Microsoft Excel. Anal. Biochem. 360, 309–311 (2007).
Bárány-Wallje, E. et al. A critical reassessment of penetratin translocation across lipid membranes. Biophys. J. 89, 2513–2521 (2005).
Keller, S., Böthe, M., Bienert, M., Dathe, M. & Blume, A. A simple fluorescence-spectroscopic membrane translocation assay. ChemBioChem 8, 546–552 (2007).
Keller, S., Tsamaloukas, A. & Heerklotz, H. A quantitative model describing the selective solubilization of membrane domains. J. Am. Chem. Soc. 127, 11469–11476 (2005).
Schmidt, M.F., El-Dahshan, A., Keller, S. & Rademann, J. Selective identification of cooperatively binding fragments in a high-throughput ligation assay enables the evolution of a picomolar caspase-3 inhibitor. Angew. Chem. Int. Ed. 48, 6346–6349 (2009).
Keller, S. et al. Membrane-mimetic nanocarriers formed by a dipalmitoylated cell-penetrating peptide. Angew. Chem. Int. Ed. 44, 5252–5255 (2005).
Keller, S., Heerklotz, H., Jahnke, N. & Blume, A. Thermodynamics of lipid membrane solubilization by sodium dodecyl sulfate. Biophys. J. 90, 4509–4521 (2006).
Heerklotz, H., Tsamaloukas, A.D. & Keller, S. Monitoring detergent-mediated solubilization and reconstitution of lipid membranes by isothermal titration calorimetry. Nat. Protoc. 4, 686–697 (2009).
Keller, S., Heerklotz, H. & Blume, A. Monitoring lipid membrane translocation of sodium dodecyl sulfate by isothermal titration calorimetry. J. Am. Chem. Soc. 128, 1279–1286 (2006).
Geissler, D. et al. (Coumarin-4-yl)methyl esters as highly efficient, ultrafast phototriggers for protons and their application to acidifying membrane surfaces. Angew. Chem. Int. Ed. 44, 1195–1198 (2005).
Hagen, V. et al. Coumarinylmethyl esters for ultrafast release of high concentrations of cyclic nucleotides upon one- and two-photon photolysis. Angew. Chem. Int. Ed. 44, 7887–7891 (2005).
Cambridge, S.B., Geissler, D., Keller, S. & Cürten, B. A caged doxycycline analogue for photoactivated gene expression. Angew. Chem. Int. Ed. 45, 2229–2231 (2006).
Gilbert, D. et al. Caged capsaicins: new tools for the examination of TRPV1 channels in somatosensory neurons. ChemBioChem 8, 89–97 (2007).
Sauer, I. et al. Dipalmitoylation of a cellular uptake-mediating apolipoprotein E-derived peptide as a promising modification for stable anchorage in liposomal drug carriers. Biochim. Biophys. Acta. 1758, 552–561 (2006).
Tsamaloukas, A.D., Keller, S. & Heerklotz, H. Uptake and release protocol for assessing membrane binding and permeation by way of isothermal titration calorimetry. Nat. Protoc. 2, 695–704 (2007).
Seelig, J. Titration calorimetry of lipid–peptide interactions. Biochim. Biophys. Acta. 1331, 103–116 (1997).
Seelig, J. Thermodynamics of lipid–peptide interactions. Biochim. Biophys. Acta. 1666, 40–50 (2004).
Forrest, S. Genetic algorithms: principles of natural selection applied to computation. Science 261, 872–878 (1993).
Motulsky, H.J. & Ransnas, L.A. Fitting curves to data using nonlinear regression: a practical and nonmathematical review. FASEB J. 1, 365–374 (1987).
Johnson, M.L. Evaluation and propagation of confidence intervals in nonlinear, asymmetrical variance spaces. Analysis of ligand-binding data. Biophys. J. 44, 101–106 (1983).
Johnson, M.L. & Frasier, S.G. Nonlinear least-squares analysis. Methods Enzymol. 117, 301–342 (1985).
Johnson, K.A., Simpson, Z.B. & Blom, T. FitSpace Explorer: an algorithm to evaluate multidimensional parameter space in fitting kinetic data. Anal. Biochem. 387, 30–41 (2009).
Michaelis, L. & Menten, M.L. Die Kinetik der Invertinwirkung. Biochem. Z. 49, 333–369 (1913).
Lineweaver, H. & Burk, D. The determination of enzyme dissociation constants. J. Am. Chem. Soc. 56, 658–666 (1934).
Berg, J.M., Tymoczko, J.L. & Stryer, L. Biochemistry 5th edn. (W.H. Freeman & Company, New York, 2002).
Wisniak, J. & Polishuk, A. Analysis of residuals—a useful tool for phase equilibrium data analysis. Fluid Phase Equilib. 164, 61–82 (1999).
Greenfield, N.J. Using circular dichroism collected as a function of temperature to determine the thermodynamics of protein unfolding and binding interactions. Nat. Protoc. 1, 2527–2535 (2006).
Pace, C.N., Grimsley, G.R., Thomas, S.T. & Makhatadze, G.I. Heat capacity change for ribonuclease A folding. Protein Sci. 8, 1500–1504 (1999).
Acknowledgements
We thank Professor Heiko Heerklotz (University of Toronto, Canada), Dr. Alekos Tsamaloukas (Rensselaer Polytechnic Institute, USA), Natalie Bordag (FMP) and Martin Kemmer (23karat GmbH, Berlin, Germany) for helpful discussions and constructive comments on the manuscript. This work was supported by grant KE 1478/1-1 from the Deutsche Forschungsgemeinschaft (DFG) to S.K.
Author information
Authors and Affiliations
Contributions
G.K. designed and performed experiments, analyzed and fitted data, and wrote the manuscript. S.K. designed experiments, analyzed data and wrote the manuscript.
Corresponding author
Rights and permissions
About this article
Cite this article
Kemmer, G., Keller, S. Nonlinear least-squares data fitting in Excel spreadsheets. Nat Protoc 5, 267–281 (2010). https://doi.org/10.1038/nprot.2009.182
Published:
Issue Date:
DOI: https://doi.org/10.1038/nprot.2009.182
This article is cited by
-
The efficacy of two methods for extracting fucoidan from frozen Arctic algae thalli: chemical composition, kinetic study and process optimization
Journal of Applied Phycology (2024)
-
γ-2 and GSG1L bind with comparable affinities to the tetrameric GluA1 core
Cellular & Molecular Biology Letters (2023)
-
Enrichment and characterization of a nitric oxide-reducing microbial community in a continuous bioreactor
Nature Microbiology (2023)
-
Preliminary insights from hydrological field monitoring for the evaluation of landslide triggering conditions over large areas
Natural Hazards (2023)
-
Fitting models for a grain size distribution: a review
Bulletin of Engineering Geology and the Environment (2023)
Comments
By submitting a comment you agree to abide by our Terms and Community Guidelines. If you find something abusive or that does not comply with our terms or guidelines please flag it as inappropriate.