Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Formula Help Involving Comparing Distance With Lat. Long. Reference Points

  1. #1
    New Member
    Join Date
    Feb 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Formula Help Involving Comparing Distance With Lat. Long. Reference Points

    Hello Everyone,

    I'm stumped on this one...

    Would anyone know a formula that would return the values shown in the example attached?

    I'm trying to find out how many people live within 25 miles and within 50 miles of a store, but my reference points are lat.long. coordinates.

    Any help would be greatly appreciated!

    Thanks





  2. #2
    Board Regular Kenneth Hobson's Avatar
    Join Date
    Feb 2007
    Location
    Tecumseh, OK
    Posts
    2,916
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Help Involving Comparing Distance With Lat. Long. Reference Points

    Welcome to the forum!

    Install Chip Pearson's routine as commented into a Module and my routine at the end or in another Module. Use my function to suit as shown in test sub or commented UDF.
    Code:
    Sub CountLatLongLimits()
      MsgBox CountLLL(25#, CDbl([C2].Value), CDbl([B2].Value), Range("H2:H10"))
    End Sub
    
    '=CountLLL(25,C2,B2,H2:H11)
    Function CountLLL(L As Double, lat1 As Double, lon1 As Double, rLatLon As Range) As Double
      Dim d As Double, gcd As Double, c As Range
      Application.Volatile (True)
      For Each c In rLatLon
         'http://www.cpearson.com/excel/LatLong.aspx
        gcd = GreatCircleDistance(lat1, lon1, c.Value, c.Offset(, 1).Value, True, True)
        'Debug.Print c.Address, gcd
        If gcd <= L Then d = d + 1
      Next c
      CountLLL = d
    End Function
    Last edited by Kenneth Hobson; Feb 26th, 2019 at 10:55 PM.

  3. #3
    Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    3,166
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Formula Help Involving Comparing Distance With Lat. Long. Reference Points

    Cheers
    Paul Edstein
    [MS MVP - Word]

  4. #4
    New Member
    Join Date
    Feb 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Help Involving Comparing Distance With Lat. Long. Reference Points

    Worked perfectly...thank you!!

    I do have one more question though if you don't mind.

    I need to run the same counts but instead of "within 25 miles" I need "within a 30 min drive"

    Is there a routine built for calculating distance in "time" (I'm guessing via Google maps), instead of "miles"?

    Thanks in advance!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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