Clever Geek Handbook

Zip code distance in excel

I am looking for a way to get a function in excel to get the distance from one zip to another. Let's say I have a list of 100,000 possible recruits and I want to sort by nearest. Id has a conference zip code, followed by 100,000 recruits zip codes. I'm looking to be able to just write a function = ZipCodeDistance (a2, d1) and then sort by that column. I found a $ 65 program that claims to do this, but I was wondering if there is a free way.

+1


source to share


3 answers


Once you have latitude and longitude for each zip code, you can use trigonometry to calculate distance like a crow.

There is a free database here that you can download.



To perform the calculation, see the information here . And some actual Excel examples used as an example are here .

+4


source


How to get the data and algorithm is explained here , but in C #. If your Excel VBA is fine, shouldn't it be too hard to translate?



+1


source


You can get a list of post office addresses for each zip code and geocode each address into a long lat pair. Do the same for the receiving address, and then use the long circle distance formula.

Here's a link to the US zip database:

http://www.zipcodeworld.com/

And another way to calculate the distance between two points on the globe:

http://williams.best.vwh.net/avform.htm

+1


source






More articles:


All Articles