Regression Tool in MS Excel® Spreadsheets for Biological Data: R-BioXL

Year 2024, Volume: 22 Issue: 3, 224 - 235, 18.12.2024

Hasan Basri Öksüz Sencer Buzrul

https://doi.org/10.24323/akademik-gida.1603881

Cited By: 1

Abstract

A user-friendly MS Excel® spreadsheet as a freeware (R-BioXL) was developed to fit mathematical models to experimental data. (R-BioXL is available to everyone at https://drive.google.com/drive/folders/1GyjT3Z_CJQZu6ASb4LQBlS-ajLa_nF6X?usp=sharing) Initially, users are expected to enter their X-Y data and define their parameters of the model. Then, a model equation should also be entered again by users. Users can visualize data (scatter plot) and model fit (line plot) with the defined initial estimates of parameters on the same graph by default. Squared differences between experimental data and model estimates are calculated automatically. Users can change the initial estimates of the parameters to make the model closer to the data instantly, and Solver Add-In of Excel® should be used to minimize the sum of squared error by changing the parameter values. After the parameters are obtained, standard errors (by using “SolverAid” macro), 95 and 99% confidence intervals of the parameters, p values to determine the statistical significance of the parameters, and goodness-of-fit indices are calculated as the last step. All results can be saved on a different Excel® working page. Whole procedure takes a couple of minutes (~3 to 10 min) depending on the Excel® experience of the user. The utility, accuracy and reliability of the spreadsheet was shown by applying two-parameter (non-linear) Michealis-Menten equation for enzyme kinetics, three-parameter (linear) van Deemter equation for chromatography, and four-parameter (non-linear) modified Gompertz equation for microbial growth. In conclusion, R-BioXL can be safely and freely used to describe the experimental data with Excel® knowledge, without any skills in programming and without additional cost for other software package.

Keywords

Curve fitting , Freeware , Mathematical modeling , Solver , SolverAid

Biyolojik Veriler İçin MS Excel® Hesap Çizelgesi Aracı: R-BioXL

Abstract

Deneysel verilere matematik modelleri uydurmak için ücretsiz yazılım olarak kullanıcı dostu bir MS Excel® hesap çizelgesi aracı (R-BioXL) geliştirilmiştir. (R-BioXL https://drive.google.com/drive/folders/1GyjT3Z_CJQZu6ASb4LQBlS-ajLa_nF6X?usp=sharing bağlantısından herkese açıktır) Başlangıçta, kullanıcıların X-Y verilerini bu araca girmeleri ve model parametrelerini tanımlamaları beklenmektedir. Daha sonra model denkleminin de yine kullanıcılar tarafından girilmesi gerekmektedir. Kullanıcılar verileri (dağılım grafiği) ve model uyumunu (çizgi grafiği) girilen ilk parametre değerleri ile aynı grafik üzerinde varsayılan olarak gözlemleyebilmektedirler. Deneysel veriler ve model tahminleri arasındaki farkların karesi otomatik olarak hesaplanmaktadır. Kullanıcılar modeli verilere anında daha yakın hale getirmek için parametrelerin ilk değerlerini değiştirebilir. Excel®’in Çözücü eklentisi parametre değerlerini değiştirerek hataların karesinin toplamını en aza indirmek için kullanılmalıdır. Parametreler elde edildikten sonra son adım olarak standart hatalar (“SolverAid” makrosu kullanılarak), parametrelerin %95 ve %99 güven aralıkları, parametrelerin istatistiksel anlamlılığını belirlemek için p değerleri ve uyum iyiliği indeksleri hesaplanır. Tüm sonuçlar farklı bir Excel® çalışma sayfasına kaydedilebilir. Tüm bu prosedür, kullanıcının Excel® deneyimine bağlı olarak birkaç dakika (~3 ila 10 dakika) sürebilir. Aracın kullanımı, doğruluğu ve güvenilirliği enzim kinetiği için iki parametreli (doğrusal olmayan) Michealis-Menten denklemi, kromatografi için üç parametreli (doğrusal) van Deemter denklemi ve mikrobiyal büyüme için dört parametreli (doğrusal olmayan) modifiye Gompertz denklemi uygulanarak gösterilmiştir. Sonuç olarak, R-BioXL, Excel® bilgisi ile deneysel verileri tanımlamak için, herhangi bir programlama becerisi gerektirmeden ve diğer yazılım paketleri için ek maliyet olmadan güvenle ve serbestçe kullanılabilir.

Keywords

Eğri uydurma , Ücretsiz yazılım , Matematik modelleme , Çözücü , SolverAid

References

• [1] Hu, W., Xie, J., Chau, H.W., Si, B.C. (2015). Evaluation of parameter uncertainties in nonlinear regression using Microsoft Excel Spreadsheet. Environmental Systems Research, 4, 4.
• [2] Serment-Moreno, V. (2021). Microbial Modeling Needs for the Nonthermal Processing of Foods. Food Engineering Reviews, 13, 465–489.
• [3] Leylak, C., Yurdakul, M., Buzrul, S. (2020). Use of Excel in food science 1: Linear regression. Food and Health, 6, 186–198.
• [4] Brown, A.M. (2001). A step-by-step guide to non-linear regression analysis of experimental data using a Microsoft Excel spreadsheet. Computer Methods and Programs Biomedicine, 65, 191–200.
• [5] Kemmer, G., Keller, S. (2010). Nonlinear least-squares data fitting in Excel spreadsheets. Nature Protocols, 5, 267–281.
• [6] Yurdakul, M., Leylak, C., Buzrul, S. (2020). Use of Excel in food science 2: Non-linear regression. Food and Health, 6, 199–212.
• [7] van Boekel, M.A.J.S. (2022). Kinetics of heat-induced changes in dairy products: Developments in data analysis and modelling techniques. International Dairy Journal, 126, 105187.
• [8] van Boekel, M.A.J.S. (1996). Statistical aspects of kinetic modeling for food science problems. Journal of Food Science, 61, 477–485.
• [9] de Levie, R. (2004). Advanced Excel for scientific data analysis. New York, USA, Oxford University Press.
• [10] Chase, A.M., von Meier, H.C., Menna, V.J. (1962). The non-competitive inhibition and irreversible inactivation of yeast. Journal of Cellular and Comparative Physiology, 59, 1–13.
• [11] van Boekel, M.A.J.S. (2008). Kinetic Modeling of Reactions in Foods. Boca Raton, CRC Press.
• [12] Moody, H.W. (1982). The evaluation of the parameters in the van Deemter equation. Journal of Chemical Education, 59, 290–291.
• [13] Lambert, R.J.W., Mytilinaios, I., Maitland, L., Brown, A.M. (2012). Monte Carlo simulation of parameter confidence intervals for non-linear regression analysis of biological data using Microsoft Excel. Computer Methods and Programs Biomedicine, 107, 155–163.
• [14] Zwietering, M.H., Jongenburger, I., Rombouts, F.M., Van’t Riet, K. (1990). Modeling of the bacterial growth curve. Applied Environmental Microbiology, 56, 1875–1881.
• [15] Alcantara, I.M., Naranjo, J., Lang, Y. (2022). Model selection using PRESS statistic. Computational Statistics, 38, 285–298.
• [16] Öksüz, H.B., Buzrul, S. (2020). Monte Carlo analysis for microbial growth curves. Journal of Microbiology, Biotechnology and Food Sciences, 10, 418–423.
• [17] de Levie R (2012). Collinearity in least-squares analysis. Journal of Chemical Education, 89, 68–78.
• [18] de Levie R (2012). Nonisothermal analysis of solution kinetics by spreadsheet simulation. Journal of Chemical Education, 89, 79–86.
• [19] Bergtold, J,S,, Pokharel, K.P., Featherstone, A.M., Mo, L. (2018). On the examination of the reliability of statistical software for estimating regression models with discrete dependent variables. Computational Statistics, 33, 757–786.
• [20] McCullough, B.D., Wilson, B. (1999). On the accuracy of statistical procedures in Microsoft Excel 97. Computational Statistics and Data Analysis, 31, 27–37.
• [21] McCullough, B.D., Wilson, B. (2000). On the accuracy of statistical procedures in Microsoft Excel 2000 and Excel XP. Computational Statistics and Data Analysis, 40, 713–721.
• [22] McCullough, B.D., Wilson, B. (2005). On the accuracy of statistical procedures in Microsoft Excel 2003. Computational Statistics and Data Analysis, 49, 1244–1252.
• [23] McCullough, B.D., Heiser, D.A. (2008). On the accuracy of statistical procedures in Microsoft Excel 2007. Computational Statistics and Data Analysis, 52, 4570–4578.
• [24] Mélard, G. (2014). On the accuracy of statistical procedures in Microsoft Excel 2010. Computational Statistics, 29, 1095–1128.
• [25] Geeraerd, A.H., Valdramidis, V.P., van Impe, J.F. (2005). GInaFiT, a freeware tool to assess non-log-linear microbial survivor curves. International Journal of Food Microbiology, 102, 95–105.
• [26] Baranyi, J., Roberts, T.A. (1994). A dynamic approach to predicting bacterial growth in food. International Journal of Food Microbiology, 23, 277–294.
• [27] Johnson, M.L. (1992). Why, when, and how biochemists should use least squares. Analytical Biochemistry, 206, 215–225.
• [28] Buzrul, S. (2021). Monte Carlo simulation in Microsoft Excel: Confidence intervals of model parameters for non-linear regression used in food sciences. Akademik Gıda, 19, 291–299.
• [29] Press, W.H.,. Teukolsky, S.A., Vetterling, W.T., Flannery, B.P. (1989). Numerical Recipes: The Art of Scientific Computing. Cambridge University Press, New York.
• [30] Straume, M., Johnson, M.L. (1992). Monte Carlo Method for determining complete confidence probability distributions of estimated model parameters. Methods in Enzymology, 210, 117–129.
• [31] Öksüz, H.B., Buzrul, S. (2021). An Excel-based, user-friendly freeware tool to describe microbial growth curves: ÖK-BUZ GRoFiT. Journal of Tekirdag Agricultural Faculty, 18, 521–532
• [32] Buzrul, S. (2024). Fen Bilimleri ve Mühendislik Uygulamalarında Deneysel Verilerin Matematik Modellerle Tanımlanması. Excel Uygulamalı Anlatım. Ankara, Türkiye, Akademisyen Kitabevi.