#1
|
|||
|
|||
Calculate trip distance in Excel
Dear all,
I am having trouble in finding the right formula in a cell to calculate the trip distance between 2 destinations in Excel, does it have to link to the Googlemap? Thank you. |
#2
|
||||
|
||||
Hi and welcome
how do you want to calculate the distance ? Orthodromy, as the crow flies?
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
||||
|
||||
For Great Circle distances, the following give good approximations:
=ACOS(SIN(RADIANS(Lat1))*SIN(RADIANS(Lat2))+COS(RA DIANS(Lat1))*COS(RADIANS(Lat2))*COS(RADIANS(Long1-Long2)))*RadiusEarth and more accurately for very small differences in Lat/Lon, particularly near the poles: =2*ASIN(SQRT((SIN(RADIANS(Lat1-Lat2)/2))^2+COS(RADIANS(Lat1))*COS(RADIANS(Lat2))*(SIN(R ADIANS(Long1-Long2)/2))^2))*RadiusEarth where the latitude and longitude coordinates are input as +/- decimal degrees and the earth's radius is given in miles or km, depending on which units you're using. When mixing hemispheres, enter Northern and Western coordinates as positive, and Southern and Eastern coordinates as negative.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#4
|
|||
|
|||
Quote:
HUH ??? I consider myself to be fairly smart but this formula and its use certainly has me stumped. Could you provide an example using your formula ? Thank you so much sir. |
#5
|
||||
|
||||
For example, for flights from Sydney (SYD) to London Heathrow (LHR), then to Los Angeles (LAX) and, finally, back to Sydney (SYD), using :
Code:
Distance From Lat1 Lon1 To Lat2 Lon2 (mi) (km) SYD -33.94610 151.17700 LHR 51.47060 -0.46194 10570 17011 LHR 51.47060 -0.46194 LAX 33.94250 -118.40800 5440 8755 LAX 33.94250 -118.40800 SYD -33.94610 151.17700 7490 12054 For a practical implementation see: https://www.msofficeforums.com/excel...using-lat.html
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#6
|
|||
|
|||
Thank you sir !
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Why does Excel seemingly always calculate the wrong R^2 value in graphs? | neager543 | Excel | 3 | 04-10-2022 12:10 AM |
Excel always freezes when I try to re-calculate a specific workbook | Chibiberu | Excel | 2 | 04-06-2019 10:35 PM |
How to create a formula in excel that can calculate a commission by sales tier ? | Skylark | Excel Programming | 1 | 05-06-2017 03:45 AM |
Is there a way in Excel to type a person's name in a cell and have it calculate their gross pay? | jllee14 | Excel | 12 | 02-26-2017 08:33 AM |
Word - Calculate and paste values from Excel sheet | Augf87 | Word | 1 | 07-06-2009 10:26 AM |