Regression Significance or P factor

G

Glen Millar

Hi,

I have two arrays of data that I need to run a regression on. I can use the
"Slope" formula to get the line slope, or x factor coefficient from the
regression statistics. But what formula can I use to get the P- factor, Eg.
0.05, or the significance at 0.05?

If I can crack that, I can do the vba to get it retuned from the number of
spreadsheets I have to analyse.

--

Regards,

Glen Millar
Microsoft PPT MVP
http://www.powerpointworkbench.com/
Please tell us your ppt version, and get back to us here
Remove spaces from signature
Posted to news://msnews.microsoft.com
 
J

Jerry W. Lewis

I know that you cracked it, but you did not post your solution for the
benefit of others who may have the same question. The p-value for
testing slope significance is

=TDIST(ABS(slope)/se1,df,2)

where df is n-2, and se1 is returned in the first column, second row of
the array returned by =LINEST(y,x,,TRUE). Alternately,

http://groups.google.com/[email protected]

provides formulas to calculate slope and se1 in ways that are
numerically better than the implementation of LINEST in pre-2003 versions.

Jerry
 
G

Glen Millar

Jerry,

I found the same thing in a different place:

http://tinyurl.com/6ch5c

I had a directory of 5043 files I needed to analyse and then do four
different regressions on summaries of each file. So I loaded each file, did
the summary and then the formulae for the regressions. I needed slope and
significance. Total of 20, 172 regressions! Thanks.

--

Regards,

Glen Millar
Microsoft PPT MVP
http://www.powerpointworkbench.com/
Please tell us your ppt version, and get back to us here
Remove spaces from signature
Posted to news://msnews.microsoft.com
 
J

Jerry W. Lewis

You are welcome. Thanks for letting me know that my post has taken on a
larger life.

Jerry
 
Top