calculating r-squared

karate718

Board Regular
Joined
Feb 25, 2005
Messages
93
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%
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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.
 
Upvote 0
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...
 
Upvote 0
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?
 
Upvote 0
How do I insert the ^ symbol in a formula?

I've searched high and low, and cannot find it anywhere!
 
Upvote 0
Not sure I understand the question - you insert it in a formula the same way you inserted it in your question - Shift+6.
 
Upvote 0
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!
 
Upvote 0
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. :LOL:
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top