Accuracy of LINEST (vs Matrix Calculations)

**teresajf** · 05-18-2011, 05:03 PM

Hi,

I am trying to code a program in VBA that will calculate the SSE for a set of response variables(Y values) and corresponding explanatory variables(Xs) - among many other things!

Because I realized that LINEST has a max number of explanatory variables of only 17, I decided to use matrix multiplication to compute the values I needed.

What I discovered while doing this was that there are some matrices that LINEST seemed to be able to get values for, but when I tried performing the calculations using matrices - the X'X matrix was not invertible

So my question is - how can LINEST accurately calculate its output?

Does this make sense?

Thank you SO much for any help in advance

**shg** · 05-18-2011, 08:03 PM

How many variables?

Can you post a workbook example?

**lori_m** · 05-22-2011, 04:45 PM

Teresajf -
If you are using Excel 2010, it supports many more columns in regression functions.

Matrix inversion is not accurate if you have many columns due to problems of limited precision and collinearity. Excel uses the QR method instead to form an orthogonal basis for the regression columns, so that matrix inversion is not needed (see wikipedia for details). A related analysis was recently discussed here.

HTH, Let me know if you need more details, Lori.