A Closer Look at LINEST and Multiple Linear Regression
In a previous blog post, I walked through how Microsoft Excel calculates all of the variables for its LINEST function (i.e., multiple linear regression) except for the variable coefficients and their standard error, but promised to revisit LINEST. This blog post directly addresses how to calculate manually the variable coefficients as well as their standard error.
The attached spreadsheet utilizes numerous advanced Microsoft Excel functions (i.e., LINEST, TRANSPOSE, MMULT, and MINVERSE, ) so you may need to review these in parallel on your own to completely understand what is being done in this spreadsheet.
For fun I will use the Body Mass Index (BMI) as my equation. It it derived with the following equation:
A fictional set of data was created using this correct formula in Cells B2:D13. Assuming we did not know the correct formula though, and wanted to create a two-variable multiple linear regression, we might use LINEST to fit the data to the following equation:
Where “a” and “b” are coefficients, and “c” the intercept of the linear equation. Using LINEST in cells B16:D20, the coefficients were found to be a≈0.13837, b≈-0.70948, and c≈50.35692, resulting in the following equation:
LINEST also estimated the standard error for each coefficient as sea=0.00992 seb=0.06263, and sec=2.74630. The F-Stat at a 95% probability was determined to be 4.7 (cell H21), and the F-Stat for this correlation was 103.4 (cell C19), so the linear regression is statistically valid. In looking at the T-Stats for each coefficient (cells C21:E21), these are well above the required T-Stat (cell H16), so each coefficient is also statistically sound.
The remainder of the attached spreadsheet walks through the procedure to manually calculate the coefficients and their standard errors. Follow steps 1-11, and you will see that all of the metrics provided by LINEST can be derived manually. Match the colors to tie the manually calculated values back to their equivalent LINEST calculated value.
Microsoft Article 828533 essentially documents exactly what I have outlined here, but in a less clear fashion.
Special thanks to Dr. Arkady Shemyakin at the University of St. Thomas for providing me with a Microsoft Excel example of how to perform these calculations. The theory is easy to find in books and on the Internet, but how to apply the matrix math is not, especially for those of us who are a little rusty on our linear algebra.
©2013 Ben Etzkorn