Regression P-Value Function

[BG]

When using the data analysis toolpack to carry out regression, you enter a
Y-input range and an X-input range. The result is a lot of data, including a
p-value, contained in a number of tables. My question is, is there a function
which makes it possible to return only the regression p-value in a single
cell for the X and Y data range? If so, it would then be possible to drag the
function down the sheet to return a p-value for a number if different Y data
ranges without having to produce a large number of tables just to get a
p-value for each one.

 

[Jerry W. Lewis]

All output from the ATP regression tool is either given directly by LINEST,
or can be easily computed from LINEST output.

Assuming that you want one of the p-values that are labeled "p-value",
rather than the one labeled "Significance F", then the p-value is given by
=TDIST(t,dfe,2), where t is the corresponding "t Stat" and dfe is the
"Residual" "df" from the table above. The quantity labeled "t Stat" is the
corresponding "Coefficent" divided by its "Standard Error".

The ATP "Coeffients" and "Standard Error" values are given in rows 1 and 2
of the output of the LINEST function, and dfe is given in row 4, column 2 of
the LINEST output. You can use the INDEX function to pick off these values
to get the p-value using a single cell, like

=TDIST( INDEX(LINEST(ydata,xdata,const,TRUE),1,col)/
INDEX(LINEST(ydata,xdata,const,TRUE),2,col),
INDEX(LINEST(ydata,xdata,const,TRUE),4,2),2)

where col is the column number of the LINEST output that gives the desired
coefficient.

Jerry

 

[BG]

Thanks for your answer Jerry,

It was a great help. I was looking for the X variable p-value, and just
before you replied to my question I found that it could be obtained using the
FDIST function.

FDIST(x, degrees of freedom 1, degrees of freedom 2)

x is calculated using the degrees of freedom 2 value and, the R squared
(RSQ) value of the x and y data as follows: DOF2-RSQ/(1-RSQ)

DOF2 is the number of Y values minus 2
DOF1 is the number of groups minis 1

I have also just noticed that the X variable p-value is the same as the
Regression Significance F value.

Thanks again for you help