How to Calculate Distance Between Two GPS Coordinates in Excel

Get FREE Advanced Excel Exercises with Solutions!

In our day to day life, we feel the necessity of measuring distance in so many times. It is not so tough to calculate the distance between two GPS coordinates in Excel. I am going to elaborate 2 simple ways to calculate the distance between two GPS coordinates in Excel.
For more clarification, I will use a dataset comprising the Latitude and Longitude values of the locations Prague, Czech Republic, and Salzburg, Austria.

calculate distance between two gps coordinates excel


How to Calculate Distance between Two GPS Coordinates in Excel: 2 Simple Ways

1.  Using Arithmetic Formula  to Calculate Distance between Two GPS Coordinates

The use of the arithmetic formula is the quickest and simplest way to calculate the distance between two addresses or GPS coordinates. Now, follow the following steps for this purpose.

Steps:

  • Create a new row titled Distance (Miles).
  • Select a cell to apply the following formula:
=ACOS(COS(RADIANS(90-C5))*COS(RADIANS(90-C6))+SIN(RADIANS(90-C5))*SIN(RADIANS(90-C6))*COS(RADIANS(D5-D6)))*3959

Here,

  • The RADIANS function converts the value in Degree units into a value of Radian unit.
  • The ACOS function returns the inverse cosine of a number.

Formula Breakdown

COS(RADIANS(90-C5))*COS(RADIANS(90-C6))+SIN(RADIANS(90-C5))*SIN(RADIANS(90-C6))*COS(RADIANS(D5-D6)) – this portion provides the value using trigonometry operators.
Output: 0.999092512926254

ACOS(0.999092512926254) – ACOS function returns the inverse Cosine value.
Output: 0.0426057358212635

0.0426057358212635 *3959 – The multiplication of 3959 converts the value into Miles.
Output: 168.676108116382

  • Finally, press ENTER to have the result.

calculate distance between two gps coordinates excel

Thus, we can calculate the distance between two GPS coordinates quite easily.

Read More: How to Calculate Distance Between Two Cities in Excel


2. Using VBA to Calculate Distance between Two GPS Coordinates

We can also use VBA to calculate the distance between two addresses or GPS coordinates. It is the smartest way to do so.

Steps:

  • First of all, go to the Developer tab.
  • Choose Visual Basic from the ribbon.

  • Now, click on Insert.
  • Then, press on Module.

calculate distance between two gps coordinates excel

  • Now, Input the following VBA code in the empty space:
Public Function DistCalc(Prague_Lati As Double, Prague_Longi As Double, Salzburg_Lati As Double, Salzburg_Longi As Double)
    With WorksheetFunction
        M = Cos(.Radians(90 - Prague_Lati))
        N = Cos(.Radians(90 - Salzburg_Lati))
        O = Sin(.Radians(90 - Prague_Lati))
        P = Sin(.Radians(90 - Salzburg_Lati))
        Q = Cos(.Radians(Prague_Longi - Salzburg_Longi))
'Change 6371 to 3959 to get your result in Miles
        DistCalc = .Acos(M * N + O * P * Q) * 6371
    End With
End Function

Firstly, I used here a Public Function procedure DistCalc. Then, I set some variables M, N, O, P, and Q with certain values. I mentioned a suitable relation between the variables to define the DistCalc Function.

  • Now, select a cell to have the measured result (i.e. C8).
  • Now, apply the following formula:
=DistCalc(C5,D5,C6,D6)

Here, the DistCalc function estimates the distance between the two points.

calculate distance between two gps coordinates excel

  • Finally, hit ENTER.

This is the coolest way to calculate the distance between two GPS coordinates.

Read More: How to Calculate Miles between Two Addresses in Excel


Practice Section

You can practice here for more expertise.

calculate distance between two gps coordinates excel


Download Practice Workbook


Conclusion

In this article, I have tried to articulate 2 simple ways to calculate the distance between two GPS coordinates in Excel. I hope it will be helpful for all. For any further questions, comment below.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects.
Naimul Hasan Arif
Naimul Hasan Arif

Hello everyone, I am Naimul Hasan Arif, graduated from Bangladesh University of Engineering and Technology (BUET). I am working as an Excel and VBA Content Developer. I try to remain dedicated to my duties and give my best with my skills & knowledge.

2 Comments
  1. Thank YOU!!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo