How to Calculate Hamming Distance in Excel (2 Ideal Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

2 Examples to Calculate Hamming Distance in Excel

  • 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.


<< Go Back to Distance | Formula List | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sabrina Ayon
Sabrina Ayon

Sabrina Ayon, a Computer Science and Engineering graduate from United International University, has been an integral part of the ExcelDemy project for two years. She authored 150+ articles, excelling in instructing through visually engaging Excel tutorials. With a passion for teaching, Sabrina conducted sessions on Excel VBA, sharing her knowledge and insights with others. Currently holding the position of Project Manager for the ExcelDemy Visual Development Project, she oversees various aspects of the project, ensuring its smooth operation... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo