**Dataset Overview**

To calculate the distance between two cities, we’ll start by preparing the dataset. Let’s consider two cities: Los Angeles (a major city in California) and Pasco (located in Washington). Our goal is to find the distance between these two cities. For this, we’ll need the latitude and longitude coordinates of both cities. Fortunately, we can easily obtain this information using Excel. Let’s walk through the steps:

**STEPS**

**Select the Cells for City Names:**- Choose the cells where you want to input the city names. In our example, we’ll select cells
**B5**and**B6**.

- Choose the cells where you want to input the city names. In our example, we’ll select cells
**Access the Data Tab:**- Go to the
**Data**tab on the Excel ribbon.

- Go to the
**Choose the Geography Data Type:**- Under the
**Data Types**category, click on**Geography**.

- Under the

**Find the Latitude:**- Select the cell where you want to display the latitude (e.g.,
**B5**). - Enter the following formula:

- Select the cell where you want to display the latitude (e.g.,

`=B5.Latitude`

- Press
**Enter**to see the latitude value.

- Press

**Repeat for the Second City:**- Similarly, find the latitude for the second city (e.g.,
**B6**).

- Similarly, find the latitude for the second city (e.g.,
**Find the Longitude:**- Select a cell where you want to display the longitude (e.g.,
**B5**). - Use the following formula:

- Select a cell where you want to display the longitude (e.g.,

`=B5.Longitude`

- Press
**Enter**to get the longitude value.

- Press

**Repeat for the Second City:**- Follow the same steps to find the longitude for the second city (e.g.,
**B6**).

- Follow the same steps to find the longitude for the second city (e.g.,
**Dataset Ready:**- Now you have the latitude and longitude coordinates for both cities. Your dataset is ready for further calculations.

**Method 1 – Applying the Haversine Formula to Calculate the Distance Between Two Cities**

The **Haversine Formula** is a fundamental equation used in oceanic exploration. It allows us to calculate the distance between two locations on a spherical plane, provided we know their latitude, longitude, and the radius of the hemisphere. **GPS** devices have automated this process. Let’s follow the steps to use the formula for calculating the distance between two cities:

**STEPS**

**Select the Cell for Distance Calculation:**- Choose the cell where you want to display the distance. In our example, we’ll select cell
**C8**.

- Choose the cell where you want to display the distance. In our example, we’ll select cell
**Apply the Haversine Formula:**- Enter the following formula into the selected cell:

`=3959*ASIN(SQRT((SIN(C6-C5)/2)^2+COS(C5)*COS(C6)*(SIN(D6-D5)/2)^2))`

- Press
**Enter**to see the result.

- Press

**Result:**- By using the formula, we obtain the result
**1367.581282**(approximately).

- By using the formula, we obtain the result

**Method 2 – Computing the Distance Between Two Cities with Excel’s ACOS, SIN, COS, and RADIANS Functions**

In this method, we’ll combine several Excel functions to calculate the distance between two cities. These functions include:

**ACOS:**Returns the inverse cosine of a value.**SIN:**Returns the sine of an angle in radians.**COS:**Returns the cosine of an angle in radians.**RADIANS:**Converts radians to degrees.

**STEPS**

**Select the Cell for Distance Calculation:**- Choose the cell where you want to display the distance (e.g., cell
**C8**).

- Choose the cell where you want to display the distance (e.g., cell
**Apply the Combined Formula:**- Enter the following formula into the chosen cell:

`=ACOS(COS(RADIANS(90-C5)) *COS(RADIANS(90-C6)) +SIN(RADIANS(90-C5)) *SIN(RADIANS(90-C6)) *COS(RADIANS(D5-D6))) *6371`

- Press
**Enter**to view the outcome.

- Press

**Result:**- By applying the formula, we arrive at the distance of approximately
**1357.033633**.

- By applying the formula, we arrive at the distance of approximately

**Method 3 – Combining Excel’s CONCATENATE and SUBSTITUTE Functions to Get the Distance Between Two Cities**

In this method, we’ll use the **CONCATENATE **and **SUBSTITUTE **functions in Excel to create a Google Map link that shows the route between two addresses.

**STEPS**

**Select the Cell for Google Map Link:**- Choose the cell where you want to display the
**Google Map link**. In our example, we’ll select cell**C8**.

- Choose the cell where you want to display the
**Apply the Formula:**- Enter the following formula into the selected cell:

`=CONCATENATE("http://maps.google.co.uk/maps?f=d&source=s_d&saddr=",`

`SUBSTITUTE(B5," ","+"),"&daddr=",SUBSTITUTE(B6," ","+"))`

- Press
**Enter**to display the result.

- Press

**Result:**- By using this formula, you can create a link to a
**Google Map**that shows the route from**Los Angeles**to**Pasco**. The**CONCATENATE**function combines the cities into the link, and the**SUBSTITUTE**function ensures the city names are formatted correctly.

- By using this formula, you can create a link to a
**How to Use the Link:****Copy**the generated link.**Paste**it into your browser’s search box.**Explore**the map to find out how far apart these two cities are from each other.

The **CONCATENATE** and **SUBSTITUTE** functions may also be used in Excel to determine the distance between two cities on the planet.

**Method 4 – Creating a LAMBDA Function to Calculate the Distance Between Two Cities**

The **LAMBDA** function in Excel allows us to build customized functions that can be used across a worksheet and accessed by familiar names. Once defined and given a name, these functions can be utilized anywhere in our workbook.

### The Main Equation for Distance Calculation

The main equation for calculating the distance between two points on the Earth’s surface is as follows:

`<code>D=acos(sinΦ1.sinΦ2+cosΦ1.cosΦ2cosΔλ).R`

Where:

**Δλ**indicates the difference between the longitudes (**lon_2-lon_1**).**Φ**and_{1 }**Φ**sequentially indicate_{2}**lat_1**and**lat_2**.**R**indicates the radius of the surface.

### Creating the LAMBDA Function

Let’s create a LAMBDA function to calculate the distance between two cities by following these steps:

**Select Cell C8:**- Choose the cell where you want to display the result. In our example, we’ll select cell
**C8**.

- Choose the cell where you want to display the result. In our example, we’ll select cell
**Enter the Formula:**- Input the following
**LAMBDA**function into the selected cell:

- Input the following

`=LAMBDA(_lat1,_lon1,_lat2,_lon2,LET(lat_1,RADIANS(_lat1),lon_1,RADIANS(_lon1),lat_2,RADIANS(_lat2),lon_2,RADIANS(_lon2),r,6378,ACOS((SIN(lat_1)*SIN(lat_2))+(COS(lat_1)*COS(lat_2)*COS(lon_2-lon_1)))*r))(C5,D5,C6,D6)`

- Press
**Enter**to calculate the result.

- Press

**Customized Formula Using Name Manager:**- While the above formula works, it can be cumbersome to use repeatedly.
- To simplify, create a customized formula using the
**LAMBDA**function:- Go to the
**Formulas**tab on the ribbon. - Under the
**Defined Names**group, click on**Name Manager**.

- Go to the

- In the Name Manager dialog box, click
**New**.

- In the Name Manager dialog box, click

- Name the formula (e.g.,
**CityDistance**). - Enter the following formula in the
**Refers to**field:

- Name the formula (e.g.,

`=LAMBDA(_lat1,_lon1,_lat2,_lon2,LET(lat_1,RADIANS(_lat1),lon_1,RADIANS(_lon1),lat_2,RADIANS(_lat2),lon_2,RADIANS(_lon2),r,6378,ACOS((SIN(lat_1)*SIN(lat_2))+(COS(lat_1)*COS(lat_2)*COS(lon_2-lon_1)))*r))`

- Then, click
**OK**.

- Then, click

- This will take you to the
**name Manager**dialog again. - Click on the
**Close**button to complete the process.

- This will take you to the

- Now you can use the customized function
**CityDistance**in your workbook.

- By applying the formula, we arrive at the distance of approximately
**1358.524645**.

**Method 5 – Calculating the Distance Between Two Cities Using Excel VBA**

In this method, we’ll create a user-defined function in **VBA** (Visual Basic for Applications) to determine the distance between two locations. We’ll make an **API** connection to retrieve data for a location using the **Bing Map API key**. Unfortunately, **Google** does not offer a free **API** for this purpose, so we’ll demonstrate using **Bing Map**.

**STEPS**

**Create a Bing Map API Key:**- If you haven’t already, click here to create your own
**Bing Map API Key**. - Place the API key in cell
**C8**of your Excel workbook.

- If you haven’t already, click here to create your own

**Access the Developer Tab:**- Go to the
**Developer**tab on the Excel ribbon.

- Go to the
**Open the Visual Basic Editor:**- From the
**Code**category, click on**Visual Basic**to open the Visual Basic Editor. - Alternatively, press
**Alt + F11**to open the editor.

- From the

- You can also right-click on your worksheet and choose
**View Code**to access the editor.

- You can also right-click on your worksheet and choose

**Create a Module:**- In the
**Visual Basic Editor**, click on**Module**from the Insert drop-down menu. - This will create a
**new**module in your workbook.

- In the

**Copy and Paste the VBA Code:**- Copy and paste the following
**VBA****code**into the module:

- Copy and paste the following

**VBA Code**

`Option ExplicitPublic Function CityDistance(First_City As String, Second_City As String, _Target_Value As String)Dim Initial_Point As String, Ending_Point As String, _Distance_Unit As String, Setup_HTTP As Object, Output_Url As StringInitial_Point = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="Ending_Point = "&destinations="Distance_Unit = "&travelMode=driving&o=xml&key=" & Target_Value & "&distanceUnit=km"Set Setup_HTTP = CreateObject("MSXML2.ServerXMLHTTP")Output_Url = Initial_Point & First_City & Ending_Point & Second_City & Distance_UnitSetup_HTTP.Open "GET", Output_Url, FalseSetup_HTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"Setup_HTTP.Send ("")CityDistance = Round(Round(WorksheetFunction.FilterXML(Setup_HTTP.ResponseText, _"//TravelDistance"), 3), 0)End Function`

**Save the Workbook as .xlsm:**- Save the entire workbook as a macro-enabled workbook with the extension
**.xlsm**.

- Save the entire workbook as a macro-enabled workbook with the extension

**Using the CityDistance Function:**- Now you can use the custom function
**CityDistance**in your workbook. - Input the latitude and longitude coordinates of the two cities as arguments to the function.
- The result will be the distance between the cities in kilometers.

- Now you can use the custom function

**VBA Code Explanation**

**Function Name and Arguments:**- We start by defining our custom function with the name
**CityDistance**. - The function takes four arguments:
**lat1**(latitude of the first city)**lon1**(longitude of the first city)**lat2**(latitude of the second city)**lon2**(longitude of the second city)

- We start by defining our custom function with the name
**Object and String Declarations:**- We declare
**SetupHTTP**as an object and several string variables:**InitialPoint****EndingPoint****DistanceUnit****OutputUrl**

- We declare
**Setting Initial Values:**- We set the
**DistanceUnit**to kilometers (since we want the result in kilometers). - The
**InitialPoint**corresponds to the starting point of the URL link.

- We set the
**Establishing API Connection:**- We set the parameters required to establish a connection between our VBA code and the Bing Map API.

**User-Defined Function:**- The core of our code is the user-defined function.
- We calculate the distance using the Haversine formula:

`CityDistance=ACOS((SIN(RADIANS(lat1))*SIN(RADIANS(lat2)))+(COS(RADIANS(lat1))*COS(RADIANS(lat2))*COSRADIANS(lon2-lon1))))*r`

- Where:
**lat1**and**lon1**: These represent the latitude and longitude coordinates of the first point.**lat2**and**lon2**: These represent the latitude and longitude coordinates of the second point.**r**: Represents the radius of the Earth (approximately 6,371 kilometers or 3,959 miles).**SIN(RADIANS(lat1))**and**SIN(RADIANS(lat2))**: These terms calculate the sine of the latitudes of the two points after converting them from degrees to radians.**COS(RADIANS(lat1))**and**COS(RADIANS(lat2))**: These terms calculate the cosine of the latitudes of the two points after converting them from degrees to radians.**COS(RADIANS(lon2 – lon1))**: This term calculates the cosine of the difference in longitudes between the two points after converting it from degrees to radians.**ACOS(…)**: The entire expression inside the**ACOS**function calculates the dot product of the unit vectors corresponding to the two points’ coordinates.- Finally, multiplying the result by
**r**gives the actual distance between the two points. **Distance Calculation**: The result of this expression gives the distance (in the same units as the radius**r**) between the two points. If you want the distance in kilometers, use**r = 6371**(Earth’s average radius in kilometers). For miles, use**r = 3959**(Earth’s average radius in miles).

- Where:

**Saving the Workbook:**- Save the entire workbook as a macro-enabled workbook with the extension
**.xlsm.**

- Save the entire workbook as a macro-enabled workbook with the extension

**Using the CityDistance Function:**- Now you can use the custom function
**CityDistance**in your workbook. - Input the latitude and longitude coordinates of the two cities as arguments to the function.
- The result will be the distance between the cities in kilometers.

- Now you can use the custom function

**Download Practice Workbook**

You can download the practice workbook from here:

**Calculate Distance Between Two Cities.xlsm**

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