Zip Code distance in excel

Question

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

Answer 1

If you have the latitude and longitude for each zip code, you can use trigonometry to calculate the distance as the crow flies.

There is a free database here that you can download.

To do the calculation, see the info here. And some actual Excel-specific calc examples are here.

Answer 2

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

Answer 3

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

Here's a link to a U.S. zip code database:

http://www.zipcodeworld.com/

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

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