# Thread: Formula Help Involving Comparing Distance With Lat. Long. Reference Points Thanks: 0 Likes: 0

1. ## 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. ## 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)
If gcd <= L Then d = d + 1
Next c
CountLLL = d
End Function```

4. ## 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"?