I like the LINEST() function for regression results because it's dynamic. You don't need to re-run it if the data changes. To illustrate, I am using Gujarati's dataset: a hypothetical antique clock auction. The dependent variable is the price paid for an antique clock, by the winning bidder. The independent or explanatory variables are the age of the clock (X2) and the number of bidders (X3):

LINEST() returns an array. Before entering, select a RANGE. And to finish the formula, don't finish with the ENTER key. Finish with CTRL+SHIFT+ENTER.

 

A few things about the output:

• It is a three-variable regression (one dependent, Y, plus two independents). So, the d.f. are 29 (32 observations - 3) and the regression has four (not three) standard errors: there is a standard error for each variable plus a standard error for the slope coefficient.
• The first two rows are telling: 
   
   
  
• The first row contains the coefficients. In this case, the line is: Y = -1336 + 12.7(X2) + 85.8(X3)
• The second row contains corresponding standard errors (the standard deviations of the sample variation in the coefficients). We can divide the second row into the first row to obtain computed (student's) t values. For example, 85.764/8.802 = 9.7. That's far greater than 2.x, so this tells us the X3 coefficient (number of bidders) is statistically significant. In other words, its true value is probably not zero.

Here is the screencast:

Comments

1. sahara 07 Oct 2008

   wow, thanks a ton,
   it really saved a lot of time, to do the regression of multiparamter dependent variable ,

   after having it linearised in its domain, for the exponential domain, of variance.

2. JonN 29 Jan 2009

   Thank Mr. Harper!

   Well explained! 2 thumbs up for your patience and presentation!

3. alf 05 Mar 2009

   pretty sweet! this really helped. thanks for making this!

4. Tyler 06 Apr 2009

   Thanks a lot! Great work!

5. Deepika 31 Aug 2009

   Hi !
   I am unable to do this. Getting some error. Can u pls help me with this?

6. brennan 16 Sep 2009

   This was an excellent tutorial.  Thank you very much David!

7. CFWF 03 Oct 2009

   Hey, thx for the very nice video. Yet, I have one follow up question. Maybe someone can help me. Is there the possibility to do the linest funktion and tell the system to only produce specific values? For example, I have a very large dataset which would ask me to re-do this procedure 7000 times. Yet, I only need the intercept and the standard error of the regression.

   Any idea is highly appreciated!!!

   Best,
   CFWF

8. Carl Ingalls 05 Oct 2009

   What is the limit to the number of independent variables when using the LINEST function in Excel?  I was able to get it to work fine at 16, but not at all with 32.  Do you have any suggestions on how to handle a model with 96 independent variables?

9. matthew 04 Feb 2010

   That was the most helpful explaination of linest. Thank you! I particularily liked the fact that you rephrased the terminology a number of times to make the concept “stick.”

Leave a Comment

Your Name*

Your Email*

Your Comment*

Notify me of follow-up comments?

Submit Comment