Need Help with Calculating similarities using the Matching coefficient and Jaccard's coefficient methods in Excel

[sllafleur]

I have to use both methods listed in the "Topic" field using the observations below. I have searched and searched, but nothing is giving me clear instructions on how to do this.

Obeservations	Fever	Cough	Breathing difficulty	Fatigue	Headache	Loss of taste	Sore throat
1	1	0	1	1	0	1	1
2	1	1	1	0	0	1	1
3	0	1	0	0	1	1	1
4	1	1	1	0	0	1	1
5	1	0	1	1	1	1	0

 

[BadgerRoad]

Are you looking for help on how to compute the coefficients or how to implement the calculations in Excel?

 

[sllafleur]

Are you looking for help on how to compute the coefficients or how to implement the calculations in Excel?

Both because no matter what I have read about similarities, I can't seem to understand it so I need someone to break it down for me like I am a complete dummy lol.

 

[BadgerRoad]

First a disclaimer. I'm not a statistician and I don't know a great deal about either of these methods. However, my understanding is the following:
A one in your data indicates the condition is present in the observation and a zero indicates it's not present (binary data).
The coefficients are calculated in pairs of observations. In other words, you will have to calculate coefficients for observations 1-2, 1-3, 1-4 up to 4-5 which is 10 sets of coefficients.
Assuming the following notation represents the number of matching attributes (the sum where each of these conditions exist)
N(1,1) means a one is in both observations for a given condition
N(0,0) means a zero is in both observations
N(1,0) means a one is in first observation and a zero is in the second observation
N(0,1) means a zero is in first observation and a one is in the second observation

Jaccard coefficient = N(1,1) / (N(0,1) + N(1,0) + N(1,1))
Matching coefficient = (N(0,0) + N(1,1)) / (N(0,0) + N(0,1) + N(1,0) + N(1,1))

For example, for observations 1 and 2:
N(1,1) = 4
N(0,0) = 1
N(1,0) = 1

Jaccard = 4/6 =.67%
Matching = 5/7 = .71%
I have a spreadsheet with this worked out, I just need to figure out how to post it.

 

[BadgerRoad]

Forgot to include N(0,1) =1 in the above example.

 

[BadgerRoad]

Following is an example for observations 1 and 2. There may be better, more concise ways of doing this.

Jaccard.xlsx
	A	B	C	D	E	F	G	H	I
2	Obeservations	Fever	Cough	Breathing difficulty	Fatigue	Headache	Loss of taste	Sore throat
3	1	1	0	1	1	0	1	1
4	2	1	1	1	0	0	1	1
5	3	0	1	0	0	1	1	1
6	4	1	1	1	0	0	1	1
7	5	1	0	1	1	1	1	0
8
9	Observations 1 and 2:								Sum
10	Both true (1,1)	1	0	1	0	0	1	1	4
11	Both false (0,0)	0	0	0	0	1	0	0	1
12	First true, second false (1,0)	0	0	0	1	0	0	0	1
13	First false, second true (0,1)	0	1	0	0	0	0	0	1
14
15	Jaccard coeff.	67%
16	Matching coeff.	71%
Sheet1

Cell Formulas
Range	Formula
B10:H10	B10	=IF(B3=1,IF(B4=1,1,0),0)
I10:I13	I10	=SUM(B10:H10)
B11:H11	B11	=IF(B3=0,IF(B4=0,1,0),0)
B12:H12	B12	=IF(B3=1,IF(B4=0,1,0),0)
B13:H13	B13	=IF(B3=0,IF(B4=1,1,0),0)
B15	B15	=I10/(I10+I12+I13)
B16	B16	=(I10+I11)/(I10+I11+I12+I13)