-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Interrater reliability or Kappa Statistic in excel
- Thread starter Dohko
- Start date
shg
MrExcel MVP
- Joined
- May 7, 2008
- Messages
- 21,845
- Office Version
- 2010
- Platform
- Windows
Here's the example from http://en.wikipedia.org/wiki/Fleiss'_kappa (which has some numerical errors) worked in Excel:
Can you hammer your data into that format?
If not, post a link that describes what you're trying to do.
Code:
---A--- --B-- --C-- --D-- --E-- --F-- --G-- ---------------------H---------------------
1 nSubj 10 B1: =ROWS(A7:A16)
2 Total 140 B2: =SUM(B7:F16)
3 nR 14 B3: =Total / nSubj
4
5
6 Cat 1 Cat 2 Cat 3 Cat 4 Cat 5 P(i)
7 Subj 01 0 0 0 0 14 1.000 G7 and down: =(SUMSQ(B7:F7) - nR)/nR/(nR-1)
8 Subj 02 0 2 6 4 2 0.253
9 Subj 03 0 0 3 5 6 0.308
10 Subj 04 0 3 9 2 0 0.440
11 Subj 05 2 2 8 1 1 0.330
12 Subj 06 7 7 0 0 0 0.462
13 Subj 07 3 2 6 3 0 0.242
14 Subj 08 2 5 3 2 2 0.176
15 Subj 09 6 5 2 1 0 0.286
16 Subj 10 0 2 2 3 7 0.286
17 Total 20 28 39 21 32 3.780 B17 and across: =SUM(B7:B16)
18 p(i) 0.143 0.200 0.279 0.150 0.229 0.213 B18 and across: =B17/Total
19 G18: =SUMSQ(B18:F18)
20
21 Kappa 0.210 B21: =(G17/nSubj - G18) / (1 - G18)Can you hammer your data into that format?
If not, post a link that describes what you're trying to do.
Last edited:
Upvote
0
I can set it up like this.
Subject Rater
1 1 1
2 12 12
3 11 12
4 2 1
5 14 14
6 16 16
7 14 14
8 12 12
9 8 8
10 7 7
Category
Subject 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
1 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
2 0 0 0 0 0 0 0 0 0 0 0 2 0 0 0 0
3 0 0 0 0 0 0 0 0 0 0 1 1 0 0 0 0
4 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0
5 0 0 0 0 0 0 0 0 0 0 0 0 0 2 0 0
6 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2
7 0 0 0 0 0 0 0 0 0 0 0 0 0 2 0 0
8 0 0 0 0 0 0 0 0 0 0 0 2 0 0 0 0
9 0 0 0 0 0 0 0 2 0 0 0 0 0 0 0 0
10 0 0 0 0 0 0 2 0 0 0 0 0 0 0 0 0
Subject Rater
1 1 1
2 12 12
3 11 12
4 2 1
5 14 14
6 16 16
7 14 14
8 12 12
9 8 8
10 7 7
Category
Subject 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
1 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
2 0 0 0 0 0 0 0 0 0 0 0 2 0 0 0 0
3 0 0 0 0 0 0 0 0 0 0 1 1 0 0 0 0
4 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0
5 0 0 0 0 0 0 0 0 0 0 0 0 0 2 0 0
6 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2
7 0 0 0 0 0 0 0 0 0 0 0 0 0 2 0 0
8 0 0 0 0 0 0 0 0 0 0 0 2 0 0 0 0
9 0 0 0 0 0 0 0 2 0 0 0 0 0 0 0 0
10 0 0 0 0 0 0 2 0 0 0 0 0 0 0 0 0
Upvote
0
saundersjt6
New Member
- Joined
- Feb 21, 2013
- Messages
- 1
Any suggestions on how to organize data for Cohen's Kappa in Excel for the following problem - 2 observers reviewing data on 29 subjects. Each subject has 9 separate segments (columns) of data with 5 possible values. Right now I have single observer organized with the subject ID as the row values, the segment as the column values and each field has a value of 1-5. I had thought about just inserting a column to identify observer 1 and observer 2, but not sure how to calculate Kappa in Excel once I have the data set. I want Kappa values between observers for individual segments and overall.
Upvote
0
Hello and sorry for reviving such an old thread, but I am trying to calculate Fleiss' kappa and am having some problems with your proposal;
I have succesfully arranged my excel file (nR=10; nSubj=32) like you mentioned, with the aforementioned formulas. To make sure I did everything right, I copied your example in another sheet, and got the same numbers as you.
I then tried to test the Kappa calculation by inputing fake results (as in including nearly all answers in category 5, a bunch at 4, and none at 1 2 or 3), but I got really low Kappa values.
I figured, if every subject had the same values in the same categories as the others the Kappa value should be closer to 1. Am I wrong? Am I misunderstanding the concept of Fleiss' Kappa?
Thank you again and sorry for bothering you.
Upvote
0
shg
MrExcel MVP
- Joined
- May 7, 2008
- Messages
- 21,845
- Office Version
- 2010
- Platform
- Windows
Welcome to the board.
I take your point, but don't know enough statistics to explain it.
There's another example at http://www.real-statistics.com/reliability/fleiss-kappa, and the same formulas give the same result obtained there:
[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
[tr][td]
[tr][td]
[tr][td]
[tr][td]
[tr][td]
[tr][td]
[tr][td]
[tr][td]
[tr][td]
[tr][td]
[tr][td]
[tr][td]
[tr][td]
[tr][td]
[tr][td]
[tr][td]
[tr][td]
[tr][td]
[tr][td]
[tr][td]
[tr][td]
[/table]
If you figure it out, let me know.
I take your point, but don't know enough statistics to explain it.
There's another example at http://www.real-statistics.com/reliability/fleiss-kappa, and the same formulas give the same result obtained there:
[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
B
[/td][td]C
[/td][td]D
[/td][td]E
[/td][td]F
[/td][td]G
[/td][/tr][tr][td]4
[/td][td]nSubj
[/td][td]12
[/td][td][/td][td][/td][td][/td][td][/td][/tr][tr][td]
5
[/td][td]Total
[/td][td]72
[/td][td][/td][td][/td][td][/td][td][/td][/tr][tr][td]
6
[/td][td]nR
[/td][td]6
[/td][td][/td][td][/td][td][/td][td][/td][/tr][tr][td]
7
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr][tr][td]
8
[/td][td][/td][td="bgcolor:#E7E7E7"]Psychotic
[/td][td="bgcolor:#E7E7E7"]Bordeline
[/td][td="bgcolor:#E7E7E7"]Bipolar
[/td][td="bgcolor:#E7E7E7"]None
[/td][td="bgcolor:#E7E7E7"]P(i)
[/td][/tr][tr][td]
9
[/td][td="bgcolor:#E7E7E7"]Patient 01
[/td][td]4
[/td][td]2
[/td][td][/td][td][/td][td="bgcolor:#F3F3F3"]0.467
[/td][/tr][tr][td]
10
[/td][td="bgcolor:#E7E7E7"]Patient 02
[/td][td]1
[/td][td]2
[/td][td]3
[/td][td][/td][td="bgcolor:#F3F3F3"]0.267
[/td][/tr][tr][td]
11
[/td][td="bgcolor:#E7E7E7"]Patient 03
[/td][td][/td][td]1
[/td][td]4
[/td][td]1
[/td][td="bgcolor:#F3F3F3"]0.400
[/td][/tr][tr][td]
12
[/td][td="bgcolor:#E7E7E7"]Patient 04
[/td][td][/td][td]1
[/td][td]1
[/td][td]4
[/td][td="bgcolor:#F3F3F3"]0.400
[/td][/tr][tr][td]
13
[/td][td="bgcolor:#E7E7E7"]Patient 05
[/td][td]1
[/td][td][/td][td]5
[/td][td][/td][td="bgcolor:#F3F3F3"]0.667
[/td][/tr][tr][td]
14
[/td][td="bgcolor:#E7E7E7"]Patient 06
[/td][td][/td][td][/td][td]1
[/td][td]5
[/td][td="bgcolor:#F3F3F3"]0.667
[/td][/tr][tr][td]
15
[/td][td="bgcolor:#E7E7E7"]Patient 07
[/td][td]2
[/td][td]3
[/td][td][/td][td]1
[/td][td="bgcolor:#F3F3F3"]0.267
[/td][/tr][tr][td]
16
[/td][td="bgcolor:#E7E7E7"]Patient 08
[/td][td][/td][td]3
[/td][td]3
[/td][td][/td][td="bgcolor:#F3F3F3"]0.400
[/td][/tr][tr][td]
17
[/td][td="bgcolor:#E7E7E7"]Patient 09
[/td][td][/td][td]2
[/td][td]4
[/td][td][/td][td="bgcolor:#F3F3F3"]0.467
[/td][/tr][tr][td]
18
[/td][td="bgcolor:#E7E7E7"]Patient 10
[/td][td][/td][td][/td][td]3
[/td][td]3
[/td][td="bgcolor:#F3F3F3"]0.400
[/td][/tr][tr][td]
19
[/td][td="bgcolor:#E7E7E7"]Patient 11
[/td][td]4
[/td][td][/td][td]2
[/td][td][/td][td="bgcolor:#F3F3F3"]0.467
[/td][/tr][tr][td]
20
[/td][td="bgcolor:#E7E7E7"]Patient 12
[/td][td][/td][td]6
[/td][td][/td][td][/td][td="bgcolor:#F3F3F3"]1.000
[/td][/tr][tr][td]
21
[/td][td]Total[/td][td="bgcolor:#F3F3F3"]12
[/td][td="bgcolor:#F3F3F3"]20
[/td][td="bgcolor:#F3F3F3"]26
[/td][td="bgcolor:#F3F3F3"]14
[/td][td="bgcolor:#F3F3F3"]5.867
[/td][/tr][tr][td]
22
[/td][td]p(i)[/td][td="bgcolor:#F3F3F3"]0.167
[/td][td="bgcolor:#F3F3F3"]0.278
[/td][td="bgcolor:#F3F3F3"]0.361
[/td][td="bgcolor:#F3F3F3"]0.194
[/td][td="bgcolor:#F3F3F3"]0.273
[/td][/tr][tr][td]
23
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr][tr][td]
24
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr][tr][td]
25
[/td][td]Kappa[/td][td="bgcolor:#F3F3F3"]0.2968
[/td][td][/td][td][/td][td][/td][td][/td][/tr][/table]
If you figure it out, let me know.
Upvote
0
Thanks for the warm welcome! Looking forward to learn.
I finally saw what was wrong with my model: the way I tested it. Everything was running fine, but I tested it assigning all of the answers to a single category (so, in the quoted example, C9:E20 would be empty while all cells in F9:F20 would have a 6) so the P in the G column was 1 (total agreement). This "breaks" the calculation, as G:22 would get an abnormally high value (close to 1) that messed up my calculations. Once every category had at least one value the Kappa was calculated correctly.
This post has helped me immensely, so thank you very much!
Upvote
0
Similar threads
- Question
