Zehad Rian Jim
This tutorial will demonstrate how to find Great Circle Distance using the Excel formula. When we are dealing with the surface of a sphere, we need to calculate the shortest distance between two points. We can find this shortest distance by using the Great Circle Distance formula. As our earth is just like a sphere and so for transportation, we need to use the great circle distance. It shows us the shortest route and saves time and transportation costs at the same time. So, it is essential to learn how to find great circle distance using the Excel formula.
Download Practice Workbook
You can download the practice workbook from here.
What Is Great Circle Distance?
When we are dealing with the surface of a sphere, to calculate the shortest distance between two points we use the Great Circle Distance. It can be easily applicable to find out the shortest distance between any two places. To determine great circle distance, we need to use a certain formula. The formula is:
d = rcos-1[cosA.cosB.cos(X-Y) + sinA.sinB]
where,
- r represents the radius of Earth which is approximately 6400 kilometers or 3959 miles,
- A and B show the latitude of the place,
- And lastly, the longitudes are presented by X and Y.
Step-by-Step Procedures to Find Great Circle Distance Using Excel Formula
We’ll use a sample dataset overview as an example in Excel to understand easily. If you follow the steps correctly, you should learn how to find great circle distance by using the Excel formula on your own. We can learn this method by following the below steps.
Step 1: Arranging Dataset
In this case, our goal is to find a great circle distance in Excel. For this, we need a dataset. For instance, we have State in column B, their corresponding Latitudes in Column C, and Longitudes in column D. We’ll use this dataset for further explanation of this article.
Read More: Excel VBA: Calculate Distance Between Two Addresses or Coordinates
Step 2: Determining Great Circle Distance
Now, we want to determine the great circle distance between two states in Excel by using the Excel formula. We can find the whole process by following the below steps.
- First, insert the following formula in the C8 cell.
=ACOS(COS(RADIANS(90-C5))*COS(RADIANS(90-C6))+SIN(RADIANS(90-C5))*SIN(RADIANS(90-C6))*COS(RADIANS(D5-D6)))*6400
The formula has used trigonometry functions like sine and cos. Moreover, A, B, X, and Y present the angle of latitude and longitude of the two states. Then, we used the RADIAN function to convert these angles to radian units. Furthermore, we used the ACOS function which mainly represents the anti-cos function of trigonometry. By these, we have used the above-discussed formula to determine the great circle distance.
- Last, press the Enter button to get the final result which presents the great circle distance between the two states.
Hence, we have determined the great circle distance between two states in Excel.
Now we want to show you an application of finding the distance between two distinct places in a state by the great circle distance formula We can find the whole process by following the below steps.
- To begin with, we arranged a dataset like the below image. In this case, we have State, Latitude 1, Latitude 2, Longitude 1, Longitude 2, and Great Circle Distance in columns B, C, D, E, F, and G.
- In addition, insert the following formula in the G5 cell.
=ACOS(COS(RADIANS(90-C5))*COS(RADIANS(90-D5))+SIN(RADIANS(90-C5))*SIN(RADIANS(90-D5))*COS(RADIANS(E5-F5)))*6400
- Furthermore, press Enter button to get the result for this cell and then use the Fill Handle option to apply the formula to the whole column.
- Finally, you will get the final result accordingly. Each cell of the Great Circle Distance column presents the greater circle distance in the same state within two selected places.
So, we have calculated the great circle distance between two places in the same state in Excel in kilometers. If you want find the distances in miles, you should use the value 3959 instead of 6400.
Read More: How to Calculate Miles between Two Addresses in Excel (2 Methods)
Similar Readings
- How to Calculate Hamming Distance in Excel (2 Ideal Examples)
- Calculate Euclidean Distance in Excel (2 Effective Methods)
- How to Calculate Distance Between Two Cities in Excel
- Calculate Driving Distance between Two Addresses in Excel
- How to Calculate Distance between Two GPS Coordinates in Excel
How to Calculate Distance Between Two Addresses in Excel
In this portion of the article, we want to calculate the distance between two addresses in Excel by using the following steps.
- To begin with, we have State in column B, Latitude in Column C, and Longitude in column D. We want to determine the distance between these two states, and for this, we have an extra cell named Distance(Miles).
- In addition, insert the following formula in the C8 cell with the help of the combination of CONCATENATE and SUBSTITUTE.
=CONCATENATE("http://maps.google.co.uk/maps?f=d&source=s_d&saddr=",SUBSTITUTE(B5," ","+"),"&daddr=",SUBSTITUTE(B6," ","+"))
This formula will create a Google Map link of how you can travel from Ohio to Alaska. The CONCATENATE function will add the addresses in the link and the SUBSTITUTE function will place the name of the addresses.
- Finally, press Enter button to get the final result by the CONCATENATE and SUBSTITUTE functions.
- After that, use this link in your internet search bar and you will get the distance information about these two addresses.
Therefore, we have calculated the distance between the two addresses in Excel.
Read More: How to Calculate Distance in Excel with Google Maps
Conclusion
Henceforth, follow the above-described methods. Hopefully, these methods will help you learn how to find great circle distance using the Excel formula. We will be glad to know if you can execute the task in any other way. Follow the ExcelDemy website for more articles like this. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our best to solve the problem or work with your suggestions.
Related Articles
- How to Calculate Coordinates from Bearing and Distance in Excel
- Calculate Mahalanobis Distance in Excel (Step by Step)
- How to Calculate Distance Between Two Coordinates in Excel (2 Methods)
- Calculate Manhattan Distance in Excel (2 Suitable Ways)
- How to Calculate Levenshtein Distance in Excel (4 Easy Methods)
