A measure for contrasting two binary data strings is the hamming distance. Counting the number of bits that differ between two values of the same length is all that is necessary to determine the Hamming distance. Excel has some fantastic functions with which we can easily compute the Hamming distance. In this article, we will demonstrate some examples to calculate Hamming distance in Excel.

**What Is Hamming Distance?**

While comparing two equal lengths of binary strings Hamming distance is the number of points/positions where the corresponding binary strings/bits are different. It is frequently employed in different forms of mistake repair or comparison of opposing sequences of data. A measure for contrasting two binary bit sequences is the hamming distance. When comparing two binary strings of identical length is the number of bit locations where the two bits disagree. We simply calculate Hamming distance by counting which pair of adjacent numerals or locations are distinct.

**How to Calculate Hamming Distance in Excel: 2 Examples**

To calculate the hamming distance we will use the combination of **COUNT **and **SUMPRODUCT** functions. **The COUNT function** is categorized under the Excel statistical function. We use this function to determine how many entries there are in a number field that is part of a range or array of numbers. This function aids in counting the number of cells that have a number in them. **The SUMPRODUCT function** gives back the total of the sums of the products of comparable ranges or arrays. This provides the total of the products after multiplying arrays together.

In Excel, we can use the following syntax to get the **Hamming **distance between two columns.

`=COUNT(RANGE1)-SUMPRODUCT(--(RANGE1 = RANGE2))`

Here,

- The first columnâ€™s total number of observations is determined using the
**COUNT**function. - Every pairwise comparison between the columns is made by
**RANGE1 = RANGE2**, which produces a**TRUE**or**FALSE**result. **â€“ â€“**transforms**TRUE**and**FALSE**values into**0**and**1**.**SUMPRODUCT**calculates the total number of**1s**.

**1. Hamming Distance Calculation Between Binary Values**

The total of related components that are different between two matrices makes up the **Hamming **distance. As an illustration, consider the following two matrices.

**X = [10101]**

**Y = [11110]**

- Firstly, we just put the values in columns to represent them as vectors.

- Secondly, select the cell where we want to see the result of the calculation of those two binary matricesâ€™ hamming distance.
- Thirdly, insert the formula into that selected cell.

`=COUNT(B5:B9)-SUMPRODUCT(--(B5:B9 = C5:C9))`

- Finally, press the
**Enter**key to complete the calculation.

**ðŸ”Ž**** How Does the Formula Work?**

**COUNT(B5:B9):**This determines the total number of observations in column**B**. and the output is**5**.**â€“(B5:B9 = C5:C9):**This will compare matches like**B5**and**C5**,**B6**and**C6**, and so on. And gives the result of the comparison is**TRUE**or**FALSE**. In this case, the output for this part is,**TRUE;FALSE;TRUE;FALSE;FALSE**. And the hyphens (**â€”**) transform the**TRUE**and**FALSE**into**0**and**1**.**SUMPRODUCT(â€“(B5:B9 = C5:C9)):**We take the total sums of the array. So, the output is**2**.**COUNT(B5:B9)-SUMPRODUCT(â€“(B5:B9 = C5:C9)):**Finally, we can see the result of the hamming distance**5 â€“ 2****= 3**.

**2. Calculate Hamming Distance Among Numeric Matrices**

Since two equivalent elements in each vector have different values, there would be a two-element difference in the Hamming distance between the two matrices. We can also calculate the hamming distance of several numeric values which represent a matrix. In this example, we again use the same syntax of the formula. Letâ€™s take a look at the next two matrices as examples.

**X = [7, 9, 2, 4, 8]**

**Y = [2, 8, 4, 5, 3]**

- In the first place, to represent the values as vectors, we simply place them in columns.

- Further, choose the cell where you wish to view the outcome of the two numeric matricesâ€™ hamming distance computation.
- Then, put the following formula into the cell.

`=COUNT(B5:B9)-SUMPRODUCT(--(B5:B9 = C5:C9))`

- Lastly, to finish the computation, hit the
**Enter**key.

**Download Practice Workbook**

You can download the workbook and practice with them.

**Conclusion**

The above examples will assist you in calculating the** Hamming** distance in Excel. Hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback.

## Related Articles

- How to Calculate Manhattan Distance in Excel
- How to Calculate Mahalanobis Distance in Excel
- How to Calculate Levenshtein Distance in Excel
- How to Calculate Euclidean Distance in Excel
- How to Find Great Circle Distance Using Excel Formula
- How to Calculate MPG in Excel
- How to Calculate Mileage in Excel