T-statistic Function

[novakj]

Hello,

Is there an worksheet function that calculates t-stat of the regression line of 2 data series?

Say I have 2 columns of data (Y and X), I run a regression of Y on X, which gives me some slope coefficient this an be obtained by SLOPE(...) function. To estimate the significance of this slope coefficent I need t statistic. Is there a function to obtain it?

Note: I am aware that the result is obtainable from Tools - Data Analysis - Regression under the heading 't Stat'. But can I obtain it directly as a function?

Thanks!

Jiri

[johnccc]

Hi novakj,

All this looks greek to me but it might point you in the right direction.
http://techniques.geog.ox.ac.uk/mod_2/week_4/prac-4.htm
Also try Google, apparantly you are not the only one with this problem.

John

[novakj]

Hi, John,

Thanks for a good tip! I have figured it out now. Unfortunately, it is not that easy. One needs to use "LINEST(...)" formula. This is an array formula, i.e. it must be entered by pressing CTRL+SHIFT+ENTER (this is important if there are more than 1 explanatory variables X). This is what MS Help says about it:

"Calculates the statistics for a line by using the "least squares" method to calculate a straight line that best fits your data, and returns an array that describes the line. Because this function returns an array of values, it must be entered as an array formula."

So, say one has column A with Y values and column B with X values. Then it this is the way to get the slope coefficient on X:

=INDEX(LINEST(A2:A10;B2:B10;;TRUE);[B][COLOR="Red"]1[/COLOR][/B]) 

and this is the way to get its standard error:

=INDEX(LINEST(A2:A10;B2:B10;;TRUE);[COLOR="Red"][B]2[/B][/COLOR]) 

T-stat can then be calculated as = coefficient/standard error.

More can be found in MS Help file.

Hope this helps someone else too :-).

Jiri

[Wigi]

Jiri Novak (like the tennis player? : D)

If you have only one explanatory variable (and a constant term), I'd use:

=LINEST(A2:A10;B2:B10;;TRUE)/INDEX(LINEST(A2:A10;B2:B10;;TRUE);2)

without Ctrl-Shift-Enter. Simply Enter.

Wigi

[johnccc]

I still think you're all talking broken biscuits.

John