We have about 400 entries in our Starter Kit download database. We offer our Starter Kit for free in exchange for email and optional location information from downloaders. Now that we are starting to hold training events (like the one coming up in Portland) it’s becoming useful to find Starter Kit downloaders who are near certain locations.
In this case @bear requested a way to determine SK downloaders who are within 100 miles of Portland OR.
So I dropped all of our SK entries into a Google Sheet, geo-coded (that means turning address information into longitude and latitude data) the addresses, and set off to figure out how to actually determine the distance.
=ACOS(COS(RADIANS(90-N2)) *COS(RADIANS(90-$P$2)) +SIN(RADIANS(90-N2)) *SIN(RADIANS(90-$P$2)) *COS(RADIANS(O2-$Q$2))) *3958.756
What these all means? I don’t know… I’m a high school drop out. But here’s what I did: The N and O columns contain the lat/long data from our entries. P2 and Q2 have Portland OR’s lat/long. Note that in my formula I’ve marked P2 as $P$2 and Q2 as $Q$2, these are absolute references, which means that as I copy the formula down (column A) the items that have dollar signs will not change relative to the cell the formula is in.
I then set a conditional formatting to column A to mark any cells that are less than or equal to R2.
Note all distances are in Kilometers.