Monday, November 14, 2016

Creating Contingency Tables With Logical Statements In Excel

Creating Contingency Tables With Logical Statements In Excel

My version of Excel does not appear to have a function that creates a contingency table.   Add-ons do exist for this purpose but I don’t have them.   The purpose of this post today is to show how to create a contingency table in Excel.   I illustrate this problem with the Iowa Wisconsin electoral vote outcome data for the years 1844 to 2016.

The data was obtained on the maps at the web site below.



I coded the outcomes for the two states and the nation for a whole as a binary variable and placed the outcomes in the post below.


The Creation of the Iowa/Wisconsin Electoral Contingency Table In Excel:

There is a complication to this problem.   In 1924, the independent candidate won the state of Wisconsin.   This means that the table will have three columns for Wisconsin and two rows for Iowa.


Hypothetical Contingency Table
Independent Wins Wisconsin
Republican Wins Wisconsin
Democrat Wins Wisconsin
Republicans win Iowa
A
B
C
Democrats win Iowa
D
E
F


In this table the letters A, B, C, D, E, and F represent the count of outcomes in each cell.  For example, E is the number of times the Democrat won Iowa and the Republican won Wisconsin.


For all elections I create six dummy variable 1/0 for each possible outcomes.

Let’s assume the 2016 data for year, IA and WI are in cells a1, b1 and c1 as below.

Year
IA
WI
2016
0
0
a1
b1
c1


We need six logical statements placed into cells d1, e1, f1, g1, h1, and i1 respectively.

If(And(b1=0,c1=-1),1,0)

If(And(b1=0,c1=0),1,0)

If(And(b1=0,c1=1),1,0)


If(And(b1=1,c1=-1),1,0)

If(And(b1=1,c1=0),1,0)

If(And(b1=01c1=1),1,0)


Copy these logical formulas the length of the table for each election year.


At the bottom of the table sum each column.


This is what you get.

Calculation of Counts for Each Cell of the Contingency Table
Year
A
B
C
D
E
F
2016
0
1
0
0
0
0
2012
0
0
0
0
0
1
2008
0
0
0
0
0
1
2004
0
0
1
0
0
0
2000
0
0
0
0
0
1
1996
0
0
0
0
0
1
1992
0
0
0
0
0
1
1988
0
0
0
0
0
1
1984
0
1
0
0
0
0
1980
0
1
0
0
0
0
1976
0
0
1
0
0
0
1972
0
1
0
0
0
0
1968
0
1
0
0
0
0
1964
0
0
0
0
0
1
1960
0
1
0
0
0
0
1956
0
1
0
0
0
0
1952
0
1
0
0
0
0
1948
0
0
0
0
0
1
1944
0
1
0
0
0
0
1940
0
0
1
0
0
0
1936
0
0
0
0
0
1
1932
0
0
0
0
0
1
1928
0
1
0
0
0
0
1924
1
0
0
0
0
0
1920
0
1
0
0
0
0
1916
0
1
0
0
0
0
1912
0
0
0
0
0
1
1908
0
1
0
0
0
0
1904
0
1
0
0
0
0
1900
0
1
0
0
0
0
1896
0
1
0
0
0
0
1892
0
0
1
0
0
0
1888
0
1
0
0
0
0
1884
0
1
0
0
0
0
1880
0
1
0
0
0
0
1876
0
1
0
0
0
0
1872
0
1
0
0
0
0
1868
0
1
0
0
0
0
1864
0
1
0
0
0
0
1860
0
1
0
0
0
0
1856
0
1
0
0
0
0
1852
0
0
0
0
0
1
1848
0
0
0
0
0
1
1844
0
0
0
0
0
1
Total
1
25
4
0
0
14


Rearrange the numbers in the bottom row to get the contingency table used in the previous blog, which was created in STATA>



The previous post analyzes the contingency table, looks at the probability the two states have the same or different outcomes, and conduct chi-square tests of independence. 
=

I remain astonished that the Clinton team, aware that she was behind in Iowa, chose to not even visit Wisconsin because she thought the Badger state was in the bag.

No comments:

Post a Comment