+ Reply to Thread
Results 1 to 3 of 3

Excel 2007 : Distance Formula with Lat/Long

  1. #1
    Registered User
    Join Date
    05-16-2012
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    1

    Exclamation Distance Formula with Lat/Long

    Hello,
    I've red some previous thread's, but didn't help.
    I'm trying Make things a bit easier by calculating distance using the "Spherical Law of Cosines" formula using Lat and Long between two points on the Earth (Sphere).

    My Formula is: cos C = (cos A) (cos B) + (sin A) (sin B) cos c
    therefore: =ACOS(SIN(lat1)*SIN(lat2)+COS(lat1)*COS(lat2)*COS(lon2-lon1))*6371 which gives the distance in km's (not a big deal to be in kilometers, I can change later to Nautical Miles)

    However, I get a #VALUE in the distance field because, I've formatted my cells to this: Lat: 00""00.0"' N" and Long: 000""00.0"' W"
    All I have to enter for Lat is: 4914.4 and show's Latitude 4914.4' N and Longitude: 5344.5 for 053""44.5' W

    I've tried to re-format the cells to DDMM.m and it wants to put it in Date format

    How can I enter and format the cells so that they will display in coordinates and also calculate distance?

    I'm already dealing with DD:MM.m or DD:MM:SS as I'm using a Navigation Chart to get Lat and Long, and trying to get my distance in Nautical Miles.

    Thanks in Advance.

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Distance Formula with Lat/Long

    I think you have to use radians: so Pi/180 times the latitude or longitude converted to decimal
    (degrees would be the same) but Minutes must be divided by 60 and Seconds by 3600)
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Distance Formula with Lat/Long

    Have you read this:

    http://www.cpearson.com/excel/LatLong.aspx

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1