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

[GCarpenter]

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

 

[Kenneth Hobson]

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.

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

 

[Macropod]

Cross-posted at:
https://www.excelforum.com/excel-fo...-distance-with-lat-long-reference-points.html
http://www.msofficeforums.com/excel/24577-trying-find-way-show-closest-using-lat-2.html#post138976
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[GCarpenter]

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!