What formula to use to calculate mileage if I have two addresses? Is there a formula or

Question

What formula to use to calculate mileage if I have two addresses? Is there a formula or steps in order to calculate the distance from one address to another in Excel?

Answer 1

Dear @LaAiesha Jones,

Thank you for reaching out to the Microsoft 365 Q&A forum. Calculating mileage directly between two addresses in Excel is a common request, but it is important to clarify that Excel does not have a native "out-of-the-box" function (like =MILEAGE()) to calculate driving distance. This is because Excel doesn't inherently "know" the road maps or traffic patterns between two points. 

However, there are three professional ways I recommend solving this, depending on the user’s technical comfort level. 

Option 1: The "New" Excel Way (Bing Maps Add-in) 

This is the most user-friendly method and uses official Microsoft tools. 

1. Go to the Home tab and select Get Add-ins. 
2. Search for "Bing Maps" and add it. 
3. Select your cells containing the addresses. 
4. Click the Bing Maps icon in your spreadsheet. It will plot the points, and while it primarily visualizes data, it allows for quick distance reference without leaving the app. 

Option 2: Using the WEBSERVICE Function (Advanced) 

If the user wants a formula that updates automatically, they can use Excel's WEBSERVICE function to pull data from the Bing Maps API. 

The Logic: The formula sends the addresses to Bing, and Bing sends back the distance. The user will need a free Bing Maps API Key. 

The Formula Structure: 

=WEBSERVICE(“http://mywebservice.com/serviceEndpoint?searchString=Excel”)

Note: This returns a string of XML code. You would then need to use the FILTERXML function to "pluck" the specific mileage number out of that code. 

For further details, you may refer to: WEBSERVICE function - Microsoft Support 

Option 3: Power Query (The professional choice) 

For large lists of addresses, Power Query is the most robust tool. It can connect to an external API (like Google Maps or Bing Maps) to transform a column of "Address A" and "Address B" into a "Distance" column. 

I understand you're looking for a direct way to calculate mileage between addresses. While Excel doesn’t have a built-in 'Distance' button, the most efficient professional method is to use the Bing Maps Add-in for visualization, or the WEBSERVICE function if you are comfortable using an API key for automated calculations. For a simple, one-time calculation, many users find that the Bing Maps Add-in provides the quickest results without needing complex formulas. 

As a forum moderator, I don’t have the necessary tools or permissions to fully investigate Power Query forum specific issues like this. These require review by teams specializing in Power Query and Excel data models. 

To get accurate assistance, I recommend posting your question in the Power Query – Microsoft Fabric Community, where product experts can review your scenario in detail. 

You can point the customer to the Bing Maps Dev Center to get started with the necessary keys for automated formulas. 

I hope this clarifies the current situation. Please let me know if you have any further questions. 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".   

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

To calculate the mileage between two addresses in Excel, you can use the Bing Maps API to get the distance. However, Excel does not have a built-in formula to directly calculate the distance between two addresses. Instead, you would typically follow these steps:

1. Geocode the Addresses: Convert the addresses into latitude and longitude coordinates using a geocoding service (like Bing Maps).
2. Use the Haversine Formula: Once you have the coordinates, you can use the Haversine formula in Excel to calculate the distance between the two points. The formula is:

      =6371 * ACOS(COS(RADIANS(lat1)) * COS(RADIANS(lat2)) * COS(RADIANS(lon2) - RADIANS(lon1)) + SIN(RADIANS(lat1)) * SIN(RADIANS(lat2)))
   

   Where lat1 and lon1 are the latitude and longitude of the first address, and lat2 and lon2 are the latitude and longitude of the second address. The result will be in kilometers (use 3959 instead of 6371 for miles).
3. Implement in Excel: Input the coordinates into the formula in Excel to get the distance.

If you want to automate this process, you might consider creating an Excel Add-In that integrates with the Bing Maps API to fetch the distance directly based on the addresses you input.

---

References:

• Calculate a Distance Matrix
• Create An Excel Add-In to Calculate Body Mass Index (BMI)
• Open in Excel experiences FAQ
• Location.CalculateDistance Method