Deriving P-Value through LINEST Function
 

Hi all,

I was wondering if anybody knew how to derive the P-Values using =LINEST()
that are calculated using the Regressions option for the Data Analysis
Tool-Pak. I am not sure what the calculation is.

I can get the t-stats via =Mn/SEn from the LINEST array, but can not find
the P-value calculation anywhere.

Any help is greatly appreciated.
Thanks!


Best,
RJ

Answer: Deriving P-Value through LINEST Function
 

Hi RJ,

To derive the P-Values using the LINEST function in Excel, you can use the T.DIST.2T function. Here are the steps:

1. First, use the LINEST function to calculate the regression coefficients and standard errors. For example, if your data is in cells A1:B10, you can use the following formula:
   
   
   Formula:

   ---

   =LINEST(B1:B10,A1:A10,TRUE,TRUE) 


   ---

   This will return an array of values, including the regression coefficients and standard errors.
   
2. Next, calculate the t-statistics by dividing the regression coefficients by their standard errors. For example, if the regression coefficient for the first variable is in cell C1 and the standard error is in cell D1, you can use the following formula:
   
   
   Formula:

   ---

   =C1/D1 


   ---

   This will give you an array of t-statistics.
   
3. Finally, use the T.DIST.2T function to calculate the P-values. For example, if the t-statistic for the first variable is in cell E1, you can use the following formula:
   
   
   Formula:

   ---

   =T.DIST.2T(ABS(E1),n-2) 


   ---

   where n is the sample size. This will give you the two-tailed P-value for the t-statistic.
   

You can repeat this process for each variable in your regression model.

Deriving P-Value through LINEST Function
 

Mon, 28 May 2007 07:56:01 -0700 from RJ
:

I was wondering if anybody knew how to derive the P-Values using =LINEST()
that are calculated using the Regressions option for the Data Analysis
Tool-Pak. I am not sure what the calculation is.

I can get the t-stats via =Mn/SEn from the LINEST array, but can not find
the P-value calculation anywhere.

If you have the t statistic, you can then use TDIST to get the p-
value.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/