This guide will discuss how to calculate the Manhattan distance in Excel using two simple and efficient ways.
Since Excel contains several built-in functions and tools, it makes performing calculations so much easier. Instead of performing calculations manually, which can take too much time, we can easily calculate mathematics and statistical values in Excel.
In this case, we will be calculating the Manhattan distance in Excel. So the Manhattan distance is a measure that calculates the distance between two vector points.
In Excel, we can utilize the ABS function to get the Manhattan distance. Furthermore, we will perform two methods to calculate a 2D Manhattan distance and a 3D Manhattan distance.
Let’s take a sample scenario wherein we need to calculate the Manhattan distance in Excel.
Suppose you have a data set containing the coordinates of two vector points. And you are tasked to calculate the Manhattan distance of the points of each row. To easily and quickly get the Manhattan distance, you utilized the ABS function.
Before we dive into a real example of calculating Manhattan distance in Excel, let’s first learn how to write the ABS function in Excel.
The Anatomy of the ABS Function
The syntax or the way we write the ABS function is as follows:
=ABS(number)
Let’s take apart this formula and understand what each term means:
- = the equal sign is how we start any function in Excel.
- ABS() is our
ABSfunction. And this function will return the absolute value of a selected number or a number without its sign. - number is the only required argument for this function. So it refers to the real number we want to get the absolute value of.
The Anatomy of the SUM Function
The syntax or the way we write the SUM function is as follows:
=SUM(number1, [number2])
Let’s take apart this formula and understand what each term means:
- = the equal sign is how we activate any function in Excel.
- SUM() refers to our
SUMfunction. And this function is used to add all the selected numbers in a range of cells. - number1 is a required argument. So this refers to 1 to 255 numbers we want to sum. Furthermore, logical values and text are not ignored in the cell. And this included if typed as arguments.
- number2 is an optional argument. And this refers to 1 to 255 numbers we want to sum. So this serves as an additional number to add together with the number1 argument.
Great! Now we can move on to a real example of how to calculate the Manhattan distance in Excel.
A Real Example of Calculating Manhattan Distance in Excel
Let’s say we have a data set containing the coordinates of two vector points. And we need to get the Manhattan distance of each vector point. So our initial data set would look like this:
Essentially, the Manhattan distance is calculated using the formula ∑ΙAi -BiΙ wherein i refers to the ith element in each vector. So the Manhattan distance is used to measure the difference or dissimilarity between two vectors.
Furthermore, the Manhattan distance is a type of measure that calculates the distance between two points as the coordinates’ absolute differences serve as the sum. Additionally, it is the total sum of the differences between the x and y coordinates. Thus, we are trying to measure how different the two vectors are from one another.
Furthermore, we will be doing two methods. Firstly, we will calculate the 2D Manhattan distance. Then, we will calculate the 3D Manhattan distance. And both methods, we will use the ABS function.
And the ABS function is used to get the absolute value of a number. So the function will return the absolute value meaning it changes negative numbers into positive numbers. However, it does not change positive numbers into anything.
After obtaining the absolute differences of each vector point using the ABS function, we will use the SUM function to get the sum of each absolute difference.
So our final data set would look like this:
You can make your own copy of the spreadsheet above using the link attached below.
Amazing! Now let’s move on and discuss the steps of how to calculate the Manhattan distance in Excel.
How to Calculate Manhattan Distance in Excel
In this section, we will explain the step-by-step process of how to calculate the Manhattan distance in Excel. Furthermore, we will discuss how to calculate a 2D Manhattan distance and a 3D Manhattan distance. To apply this to your work, simply follow the steps below.
1. Firstly, we need to create a new column to input the absolute difference of each vector point. Next, we will type in the formula “=ABS(B3-C3)”. Lastly, we will press the Enter key to return the result.
2. Secondly, we will drag down the Fill Handle tool to copy the formula and apply it to the other cells.
3. Thirdly, we will add the absolute difference of each vector using the SUM function. So we will input the formula “=SUM(D3:D6)”. Lastly, we will press the Enter key to return the result.
4. And tada! We have successfully calculated the 2D Manhattan distance in Excel.
5. Next, let’s try calculating the 3D Manhattan distance. Firstly, we will create another column to input the absolute difference results. So type in the formula “=ABS(B11-C11)”. Finally, we will press the Enter key to return the result.
6. Then, we will drag the Fill Handle tool down to copy the formula and apply it to the other cells to get the absolute differences.
7. Afterward, we will calculate the sum of the absolute differences using the SUM function. So we will input the formula “=SUM(D11:D14)”. Finally, we will press the Enter key to return the result.
8. And tada! We have successfully calculated the 3D Manhattan distance in Excel.
And that’s pretty much it! We have discussed how to calculate the Manhattan distance in Excel. Furthermore, we explained how to calculate the 2D Manhattan distance and the 3D Manhattan distance in Excel. Now you can choose any of the two and apply it to your work whenever you need to.
Are you interested in learning more about what Excel can do? You can now use the ABS function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
