Creating Contingency Tables With Logical Statements In Excel
My version of Excel does not appear to have a function that
creates a contingency table. Addons 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
chisquare 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