Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. Member
Join Date
19th October 2005
Posts
21

## T-statistic Function

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

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: T-statistic Function

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

Excel Video Tutorials / Excel Dashboards Reports

3. Member
Join Date
19th October 2005
Posts
21

## Re: T-statistic Function

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:

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

```
and this is the way to get its standard error:

VB:
```=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

Excel Video Tutorials / Excel Dashboards Reports

4. ## Re: T-statistic Function

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

5. ## Re: T-statistic Function

I still think you're all talking broken biscuits.

John

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno