Announcement

Collapse
No announcement yet.

T-statistic Function

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #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

    Comment


    • #3
      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:

      Code:
      =INDEX(LINEST(A2:A10;B2:B10;;TRUE);1)
      and this is the way to get its standard error:

      Code:
      =INDEX(LINEST(A2:A10;B2:B10;;TRUE);2)
      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

      Comment


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

        Wigi

        Excel MVP 2011-2014

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

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

        Comment


        • #5


          Re: T-statistic Function

          I still think you're all talking broken biscuits.

          John

          Comment

          Working...
          X