I haven’t seen anywhere that does this, so here’s how to calculate the Hamming distance between two binary strings in excel.
Say you have two binary strings, say 001001 and 100100. How do you calculate their Hamming distance? It turns out it isn’t that easy in Excel, but is possible.
How do we calculate the Hamming distance between 100 and 011 in the cube above, shown as the shortest (red) line joining these two points?
To cut to the chase, the formula for calculating the Hamming distance between strings in cell A1 and B1 is:
=LEN(B1) - LEN(SUBSTITUTE(DEC2BIN(BITXOR(BIN2DEC(B1), BIN2DEC(A1)), LEN(B1)), "1", ""))
BITXOR(A1, B1) sums the difference in bits between the strings in A1 and B1 according to Exclusive OR XOR logic:
| INPUTS | OUTPUT | |
| A | B | A XOR B |
| 0 | 0 | 0 |
| 0 | 1 | 1 |
| 1 | 0 | 1 |
| 1 | 1 | 0 |
However, Excel needs these numbers as decimals, and the BIN2DEC function converts these binary strings to decimals.
And finally, a trick for counting the occurances of a character in a string:
LEN(A1)-LEN(SUBSTITUTE(A1,”1″,””)) counts the number of 1’s in the string.
As I say, quite a technical note, and I hope useful to people looking at Hamming distances.
Thanks. It helps.