India voter turnout for 2014: Using simple Excel Linear regression model

Wondering what would be the voter turnout for the year 2014 based the past history of elections in India. Here is an attempt in my post to evaluate the Linear regression model and fitting a line to data to determine what could be the possible voter turnout percentage for the year 2014.

Source of Data:

International Institute for Democracy and Electoral Assistance has the data for the voter turnout in the elections in the past in India. http://www.idea.int/vt/countryview.cfm?CountryCode=IN. We will use that as the source for this analysis. Though the elections are not held linearly this is only an attempt to predict what will the line of fit within the given data.

Step 1: Let’s put the data in a spreadsheet. For the purpose of simplicity and to bring some linearity we have added Index column along with the data.

Year

Index

Voter turnout Percentage

1952

0

61.17%

1957

1

62.23%

1962

2

55.42%

1967

3

61.04%

1971

4

55.25%

1977

5

60.49%

1980

6

56.92%

1984

7

63.56%

1989

8

61.98%

1991

9

56.73%

1996

10

57.94%

1998

11

61.97%

1999

12

59.99%

2004

13

58.07%

2009

14

58.17%

 

Step 2: Now let’s plot a scatter plot against this data as shown below. Make sure you select both the columns Index and Voter turnout percentage and select “Scatter with Only Markers” type (I’m using Office 2007). This highest voter turnout seems to be in the year 1984 with 63.56%

 

Step 3: Now let’s bring the linear regression equation to this chart, which will probably help us to evaluate what possibly could be the turnout in 2014? After selecting the chart select the “Design” Tab and look for “layout 9” which has fx along with a trend line and select that. It’s given in the picture below:

 

Step 4: After selecting the Layout 9, you will have the line of fit and its relevant equation. To have better clarity this equation has been moved on to the right.


 

Step 5: So putting things into the equation we are expecting a 59% overall turnout for 2014. Not sure, I’m also awaiting for the results. See the screenshots with the updated data in the spreadsheet.

Year to Predict

Equivalent Index

Equation

Resolving

2014

15

y = -0.0005x + 0.5974, R² = 0.0069

59%

 


Lets exercise our democratic rights and await for good governance. Also lets check the voter turnout for the year 2014. 🙂

Power Pivot with Test playing nations

I have tried to attempt to create a power pivot using the data from ESPNCricket Info. Power pivot seems to be very powerful and provides capabilities of Slicing and dicing at ease. Try your hands at it. It seems based on the historic records the number of matches which has been won and lost being equal. Australia and India has drawn more than 200 matches, where in Australia has drawn 200 matches out of 752 matches.

Interesting info. J