Grouping Zip Codes by Proximity

[Tommy2Tables365]

Hello everybody, long time reader, first time poster. I've looked everywhere in order to piece this together and I'm coming up short and I'm hoping you guys can help me out on this. In the table below I have a column of zip codes with their corresponding Latitudes and Longitudes. I would like place these zip codes into groups based on how close they are together. For example, Group A would consist of zip codes that are within 25 miles of each other, Group B would consist of all zip codes within 100 miles of each other, and Group C would consist of all zip codes that are within 150 miles of each other. Straight line distance would suffice. Thank you in advance for taking a look at this for me, I appreciate it!

 

[RobP]

Hi, welcome to the forum. Maybe you already have some maths to allow you to know which ones you want in each list - so that would be great to share.

Otherwise, my theory comes up with : group A has a second zip code which is 25 miles from the first one before it. Then the one after it is 25 miles from the second one, but possibly 50 miles from the first one in the list ... and so on ..

Which ones need to be in the list ?

cheers
Rob

 

[Tommy2Tables365]

Hi, welcome to the forum. Maybe you already have some maths to allow you to know which ones you want in each list - so that would be great to share.

Otherwise, my theory comes up with : group A has a second zip code which is 25 miles from the first one before it. Then the one after it is 25 miles from the second one, but possibly 50 miles from the first one in the list ... and so on ..

Which ones need to be in the list ?

cheers
Rob

I have a list of US 450 zip codes that should be on the list. I'm not sure how to calculate the straight-line distance between the zip codes in order to place them into the proper group, i.e. 25 miles, 50 miles, 100 miles etc.

 

[RobP]

Hi, well, google shares us this formula, which can show you the distance between 2 points on the globe using lat & long.

Result comes in km (but you can convert to miles I'm sure). In terms of the grouping, my original statement still stands in terms of how you relate each one to each group.
Let me try to clarify more for you.
If you take the first one in your list, and take that as point 1, you will need to calculate how far the other 449 are away from it using the below formula. Then you can take pojnt 2, and calculate how far each of the other 449 are away from it. Then same for point 3, point 4 etc. etc. so you will have 449 distances for each of your 450 entries. In theory, (>200,000 points of data) and each zip code can be in each group multiple times, depending on where you are comparing the distance from...

Up to you to decide how you want to interpret that data into groups ... as its not obvious..

cheers
Rob

Book1
	A	B	C	D	E
1			Latitude	Longtitude
2	Location 1		42.04	-71.87
3	Location 2		42.39	-71.56
4				46.5445	km apart
Sheet1

Cell Formulas
Range	Formula
D4	D4	=ACOS((SIN(RADIANS(C2)) * SIN(RADIANS(C3))) + (COS(RADIANS(C2)) * COS(RADIANS(C3))) * (COS(RADIANS(D3) - RADIANS(D2)))) * 6371