On The Pros and Cons of Using Excel for Regression Analysis
Excel is a useful and powerful software for simple regression analysis without any programming skills and that is why, it is widely preferred by the undergraduate students from various areas such as chemistry, biology and agriculture as well as many engineering disciplines such as chemical engineeri...
Saved in:
| Main Author: | |
|---|---|
| Format: | Article |
| Language: | English |
| Published: |
Turkish Science and Technology Publishing (TURSTEP)
2024-12-01
|
| Series: | Turkish Journal of Agriculture: Food Science and Technology |
| Subjects: | |
| Online Access: | https://agrifoodscience.com/index.php/TURJAF/article/view/6931 |
| Tags: |
Add Tag
No Tags, Be the first to tag this record!
|
| _version_ | 1846098617779093504 |
|---|---|
| author | Sencer Buzrul |
| author_facet | Sencer Buzrul |
| author_sort | Sencer Buzrul |
| collection | DOAJ |
| description | Excel is a useful and powerful software for simple regression analysis without any programming skills and that is why, it is widely preferred by the undergraduate students from various areas such as chemistry, biology and agriculture as well as many engineering disciplines such as chemical engineering, food engineering and bioengineering. Parameter values and coefficient of determination (R2) can be easily obtained together with the graphical representation for those models exist in Excel such as linear and exponential models. It is also possible to visually examine the model fit and experimental data together on the same graph. For linear models (linear in parameters) Excel Add-In Data Analysis-Regression tool creates a summary output, and parameter estimates, parameter uncertainties, adjusted R2 (R2adj) and root mean square error (RMSE) values can be found even for the models that do not exist in Excel. For nonlinear models (nonlinear in parameters) Excel Add-In Solver tool can be used to obtain parameter estimates (but not uncertainties), and R2, R2adj and RMSE can be calculated manually. Despite these advantages, there are some shortcomings of Excel for regression analysis. For linear models with no-intercept Excel reports the incorrect and overoptimistic R2 and also reports incorrect and overpessimistic R2adj. Excel has also some nonlinear models such as exponential and power models in it; however, Excel computes linear parameter estimates for those nonlinear models and again optimistic R2 is calculated. This paper aims (i) to show these inaccuracies with their reasons by using published data, (ii) how to obtain the correct results, (iii) to warn the instructors who would use Excel for regression analysis in class. Excel users (instructors, students, professionals in any field) should be aware of the pitfalls when using Excel for regression. |
| format | Article |
| id | doaj-art-2ecad5c6e231440e9d5193ea25140b27 |
| institution | Kabale University |
| issn | 2148-127X |
| language | English |
| publishDate | 2024-12-01 |
| publisher | Turkish Science and Technology Publishing (TURSTEP) |
| record_format | Article |
| series | Turkish Journal of Agriculture: Food Science and Technology |
| spelling | doaj-art-2ecad5c6e231440e9d5193ea25140b272025-01-01T15:46:48ZengTurkish Science and Technology Publishing (TURSTEP)Turkish Journal of Agriculture: Food Science and Technology2148-127X2024-12-0112s22234224110.24925/turjaf.v12is2.2234-2241.69315632On The Pros and Cons of Using Excel for Regression AnalysisSencer Buzrul0https://orcid.org/0000-0003-2272-3827Necmettin Erbakan University, Department of Food Engineering, Meram/Konya, TürkiyeExcel is a useful and powerful software for simple regression analysis without any programming skills and that is why, it is widely preferred by the undergraduate students from various areas such as chemistry, biology and agriculture as well as many engineering disciplines such as chemical engineering, food engineering and bioengineering. Parameter values and coefficient of determination (R2) can be easily obtained together with the graphical representation for those models exist in Excel such as linear and exponential models. It is also possible to visually examine the model fit and experimental data together on the same graph. For linear models (linear in parameters) Excel Add-In Data Analysis-Regression tool creates a summary output, and parameter estimates, parameter uncertainties, adjusted R2 (R2adj) and root mean square error (RMSE) values can be found even for the models that do not exist in Excel. For nonlinear models (nonlinear in parameters) Excel Add-In Solver tool can be used to obtain parameter estimates (but not uncertainties), and R2, R2adj and RMSE can be calculated manually. Despite these advantages, there are some shortcomings of Excel for regression analysis. For linear models with no-intercept Excel reports the incorrect and overoptimistic R2 and also reports incorrect and overpessimistic R2adj. Excel has also some nonlinear models such as exponential and power models in it; however, Excel computes linear parameter estimates for those nonlinear models and again optimistic R2 is calculated. This paper aims (i) to show these inaccuracies with their reasons by using published data, (ii) how to obtain the correct results, (iii) to warn the instructors who would use Excel for regression analysis in class. Excel users (instructors, students, professionals in any field) should be aware of the pitfalls when using Excel for regression.https://agrifoodscience.com/index.php/TURJAF/article/view/6931curve fitgoodness-of-fitspreadsheetsoftwaresummary output |
| spellingShingle | Sencer Buzrul On The Pros and Cons of Using Excel for Regression Analysis Turkish Journal of Agriculture: Food Science and Technology curve fit goodness-of-fit spreadsheet software summary output |
| title | On The Pros and Cons of Using Excel for Regression Analysis |
| title_full | On The Pros and Cons of Using Excel for Regression Analysis |
| title_fullStr | On The Pros and Cons of Using Excel for Regression Analysis |
| title_full_unstemmed | On The Pros and Cons of Using Excel for Regression Analysis |
| title_short | On The Pros and Cons of Using Excel for Regression Analysis |
| title_sort | on the pros and cons of using excel for regression analysis |
| topic | curve fit goodness-of-fit spreadsheet software summary output |
| url | https://agrifoodscience.com/index.php/TURJAF/article/view/6931 |
| work_keys_str_mv | AT sencerbuzrul ontheprosandconsofusingexcelforregressionanalysis |