Statistical
Jaccard Similarity Index & Hamming Distance formulas (SUMPRODUCT)
Compare binary feature sets and equal-length codes via Jaccard Similarity |A n B| / |A u B| (ignoring shared 0-0 absences) and Hamming Distance mismatch count.
Why Jaccard ignores shared 0-0 absences while Hamming counts all mismatches!
Both columns encode the same five features in the same row order using numeric 0 or 1. Jaccard excludes joint absences; Hamming counts differing positions. Neither measure recognizes swapped features or treats a missing observation as absence.
By inclusion-exclusion on {0, 1} binary vectors, |A intersect B| = SUMPRODUCT(A2:A6, B2:B6) and |A union B| = SUM(A2:A6) + SUM(B2:B6) - SUMPRODUCT(A2:A6, B2:B6)! Meanwhile, Richard Hamming's Distance counts total differing bit positions (1-0 or 0-1): Hamming = SUM(A2:A6) + SUM(B2:B6) - 2 * SUMPRODUCT(A2:A6, B2:B6)!
| Feature / Bit (Row) | Vector A (A) | Vector B (B) | Set & Bitwise Role |
|---|---|---|---|
| Feature 1 | 1 | 1 | Intersection (1 & 1) -> Match |
| Feature 2 | 1 | 0 | Union only -> Hamming mismatch #1 |
| Feature 3 | 0 | 0 | Joint absence (Ignored by Jaccard!) |
| Feature 4 | 1 | 1 | Intersection (1 & 1) -> Match |
| Feature 5 | 0 | 1 | Union=4, Intersect=2 -> Jaccard=0.50, Hamming=2 |
Jaccard Similarity Index |A intersect B| / |A union B|
This implementation explicitly defines two empty sets as similarity1. That is a convention for the otherwise undefined zero-over-zero case. Missing cells and text are rejected rather than converted to zero.
=IF(OR(COUNT(A2:B6)<>10,COUNTIF(A2:B6,0)+COUNTIF(A2:B6,1)<>10),"Invalid inputs",LET(inter, SUMPRODUCT(A2:A6, B2:B6), uni, SUM(A2:A6) + SUM(B2:B6) - inter, IF(uni = 0, 1, ROUND(inter / uni, 4))))Binary Hamming Distance (mismatched bit positions)
This identity applies only to binary vectors. The result is a mismatch count from0 to5; divide by5 if a normalized fraction is wanted. It is not a general string edit distance.
=IF(OR(COUNT(A2:B6)<>10,COUNTIF(A2:B6,0)+COUNTIF(A2:B6,1)<>10),"Invalid inputs",SUM(A2:A6) + SUM(B2:B6) - 2 * SUMPRODUCT(A2:A6, B2:B6))Try the example
Use the original values or change them to test your understanding.