Page 1 of 2 12 LastLast
Results 1 to 10 of 13

calculating r-squared

This is a discussion on calculating r-squared within the Excel Questions forums, part of the Question Forums category; Hi, I'd like to calculate the r-sqaured of the S&P 500 performance in an election year to see if that ...

  1. #1
    Board Regular
    Join Date
    Feb 2005
    Posts
    86

    Default calculating r-squared

    Hi,

    I'd like to calculate the r-sqaured of the S&P 500 performance in an election year to see if that has any effect on the market's performance.

    The data I have is below. The election years start in 1928 and are every 4 greater than that. How would I set it up to use the rsq forumla?

    Thank you.

    S&P 500 Index
    1926 12%
    1927 37%
    1928 44%
    1929 -8%
    1930 -25%
    1931 -43%
    1932 -8%
    1933 54%
    1934 -1%
    1935 48%
    1936 34%
    1937 -35%
    1938 31%
    1939 0%
    1940 -10%
    1941 -12%
    1942 20%
    1943 26%
    1944 20%
    1945 36%
    1946 -8%
    1947 6%
    1948 6%
    1949 19%
    1950 32%
    1951 24%
    1952 18%
    1953 -1%
    1954 53%
    1955 32%
    1956 7%
    1957 -11%
    1958 43%
    1959 12%
    1960 0%
    1961 27%
    1962 -9%
    1963 23%
    1964 17%
    1965 12%
    1966 -10%
    1967 24%
    1968 11%
    1969 -8%
    1970 4%
    1971 14%
    1972 19%
    1973 -15%
    1974 -26%
    1975 37%
    1976 24%
    1977 -7%
    1978 7%
    1979 18%
    1980 32%
    1981 -5%
    1982 21%
    1983 23%
    1984 6%
    1985 32%
    1986 18%
    1987 5%
    1988 17%
    1989 31%
    1990 -3%
    1991 30%
    1992 8%
    1993 10%
    1994 1%
    1995 38%
    1996 23%
    1997 33%
    1998 29%
    1999 21%
    2000 -9%
    2001 -12%
    2002 -22%
    2003 29%
    2004 11%
    2005 5%
    2006 16%

  2. #2
    Board Regular AnAnalyst's Avatar
    Join Date
    Jul 2007
    Location
    Manchester, UK
    Posts
    498

    Default Re: calculating r-squared

    Easy way - create a line graph of the data - years on x axis, percentages on y axis. Add a trend line and in the trendline options ask it to display the equation and rsq value. This gives you the added value of a visual display of the data - essential, I would say.
    (Using Excel 2010 and Windows 8.1)

    Nihil est miserum nisi cum putes!

    Remember, I'm only trying to help!
    If I've helped then I've achieved happiness!
    Help me to achieve happiness by telling me if I've helped!

  3. #3
    Board Regular
    Join Date
    Feb 2005
    Posts
    86

    Default Re: calculating r-squared

    This is great. Thank you.

  4. #4
    Board Regular
    Join Date
    Oct 2006
    Posts
    92

    Default Re: calculating r-squared

    Bit late now but creating the chart and rsq value as described above doesn't tell you about the relationship between election years and the S&P index. It tells you about the relationship between the year (as a number) and the S&P index.

    To look at the relationship between election years and the S&P in terms of the r squared...let's say your years are in A1:A81 and your S&P indices in B1:B81...

    In column C, put a 1 for each election year and a 0 for each non-election year. For example, in the first 4 cells in column C put the values 0,0,1,0. In cell C5 put =B1, and then drag that formula to fill the rest of the cells.

    Then in an empty cell put =CORREL(B1:B81,C1:C81)^2. That will give you the r square.

    Luckily, your conclusions won't change much.

    For what it's worth, r square isn't a very good tool when one or more of the variables is categorical (as election year is in this case), but that takes us out of the scope of this board...

  5. #5
    Board Regular
    Join Date
    Feb 2005
    Posts
    86

    Default Re: calculating r-squared

    slomobonobo,

    Thanks very much for this. I realized as I finished this that r-squared isn't the best indicator of this. I belive it is the f-score, is that correct?

  6. #6
    Board Regular
    Join Date
    Nov 2007
    Location
    uk
    Posts
    90

    Default Re: calculating r-squared

    How do I insert the ^ symbol in a formula?

    I've searched high and low, and cannot find it anywhere!

  7. #7
    Board Regular
    Join Date
    Sep 2008
    Location
    Brisbane, Australia
    Posts
    767

    Default Re: calculating r-squared

    Quote Originally Posted by fastglass View Post
    How do I insert the ^ symbol in a formula?

    I've searched high and low, and cannot find it anywhere!
    Shift+6....?

  8. #8
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    East Sussex
    Posts
    22,813

    Default Re: calculating r-squared

    Not sure I understand the question - you insert it in a formula the same way you inserted it in your question - Shift+6.

  9. #9
    Board Regular
    Join Date
    Nov 2007
    Location
    uk
    Posts
    90

    Default Re: calculating r-squared

    lol!!!

    I've just spent about 30minutes looking for an 'alt' shortcut for this, and it's staring me right in the face on my '6' key!

    Sorry for being so dense guys!

  10. #10
    Board Regular
    Join Date
    Sep 2008
    Location
    Brisbane, Australia
    Posts
    767

    Default Re: calculating r-squared

    Quote Originally Posted by fastglass View Post
    lol!!!

    I've just spent about 30minutes looking for an 'alt' shortcut for this, and it's staring me right in the face on my '6' key!

    Sorry for being so dense guys!
    In terms of all the dense things I've done, that wouldn't make the top 20.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com