Searching points within a bounding circle in excel
Searching points within a bounding circle in excel
(OP)
I am trying to find points in an excel database that lie within a circle with a 50 mile radius. I have over 100,000 points in the database all given in lat/long form. The center of the circle will be lat/long (x,y). How do I write a macro that finds the points bounded by this formula?





RE: Searching points within a bounding circle in excel
http://www.eng-tips.com/threadminder.cfm?pid=770
RE: Searching points within a bounding circle in excel
First make sure that your coordinates uses the same units as your criteria. Then use pythagoras to calulate the distance then select if<X.
You dont even need a macro. Just add the calculation in the next collum and use conditional formatting to highlight the matching points. If you wish to remove all non match, then write a macro, do the calc and erase all the non match. Use "record macro" to quickly determine the coding for navigation and deleting a row.
Best regards
Morten
RE: Searching points within a bounding circle in excel
You can rap your distance calculation into an "if" function and have it return 0 or a 1 e.g. =1 if within your distance (circle). You can then use the filter function to produce a list, which if required could be copied into another sheet. The filter function will be very useful for large non-sequential lists.