Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: T-statistic Function

  1. #1
    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. #2
    Join Date
    23rd September 2003
    Location
    York
    Posts
    880

    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. #3
    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. #4
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,039

    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
    Regards,

    Wigi

    Excel MVP 2011, 2012, 2013



    For more Excel memes: visit http://www.wimgielis.be ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  5. #5
    Join Date
    23rd September 2003
    Location
    York
    Posts
    880

    Re: T-statistic Function

    I still think you're all talking broken biscuits.

    John

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Date Function - NOW Function - Excel 2007 NOW Function
    By Dave Hawley in forum Excel Formulas (No Questions)
    Replies: 0
    Last Post: July 10th, 2008, 02:33
  2. Date Function - NOW Function - Excel 2007 NOW Function
    By Dave Hawley in forum Excel Formulas (No Questions)
    Replies: 0
    Last Post: July 10th, 2008, 02:32
  3. Date Function - NOW Function - Excel 2007 NOW Function
    By Dave Hawley in forum Excel Formulas (No Questions)
    Replies: 0
    Last Post: July 10th, 2008, 02:31
  4. Statistic Formula - T-Stat
    By mthiel in forum EXCEL HELP
    Replies: 1
    Last Post: September 21st, 2006, 00:31

Bookmarks

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