Using Excel to map locations and distances between instances

[kdamax]

Hi,

I have a population of businesses whose post code I know. What I want is an Excel calculation that will show me the number of other businesses within say 40 square miles of their Post Code? I can get the post codes and convert them to longitude and lattitude coordinates but I dont know how to set a formaula to calculate 1) what is a 40 mile radius of that location 2) how many other business fit within that 40 mile radius?

Be great if someone could help me. This is my first post, so apologies if I havent been clear, happy to be guided.

Kind regards

Max

 

[ParamRay]

If you have two postcodes and you can convert both to longitude-latitude coordinates (like you said), then you can use the Haversine formula to calculate the distance between them. Check out this post for guidance
:
https://stackoverflow.com/questions...o-latitude-longitude-points-haversine-formula

(The example is in javascript, but it's clear enough what is going on.)

 

[jrg24]

I know this isn't ideal, but I work in transportation and there are companies that sell addins for this sort of thing. I work in transportation, and I use one called pcmiler, and I beleive they have versions for other regions of the world. it has several different types of distances (as the crow flies, shortest distance, without tolls, "practical miles", etc.) comes in pretty handy. it works as a UDF, you start typing a formula =miles( [zip code 1, zip code 2, type of miles) and returns the result. If all you have is a zip code it can give you the name of the location, though I have never used this function. (i.e. zip code 77002 is Houston, Harris County, Texas). This is typically used for trucking route planning, though it has come in handy for other uses as well. Its pricey, but if you regularly do this sort of work it may be worth looking into.