Calculating the distance between coordinates with conditions

Hi Guys,

i've hit a problem with my set of data. I've a set of GPS coordinates in Decimal Degree format. the goal is to calculate the distance between coordinates from point to multipoint.

formula to calculate distance between two coordinates:

=ROUND(6371*ACOS(COS(RADIANS(90-I19))*COS(RADIANS(90-I20))+SIN(RADIANS(90-I19))*SIN(RADIANS(90-I20))*COS(RADIANS(J19-J20)))/1.609,2)

is it possible for you to attach a workbook with more data in it (like 50 site ID)?
With this, it'll help us to come up with a solution more easily.
Make sure, some of them are close enough to trigger the highlighting or flagging.
Thanks

Hi Pierre,

Thank you for answering my post.
I've attached a sample document, of course my original document has a lot more tabs than this but the information in this document is what is needed for the script.

FYI these are not actual radio sites, just a bunch of random coords that are near each other, they are close enough to cause alarms on some points.

Here is a macro that will write in column G all the conflicting sites.

Please Login or Register  to view this content.

See attached workbook for a working example.
I'm using Sheet2 as a calculating facility. There are named ranges in there. make sure you don't erase them.

Originally Posted by p24leclerc

Here is a macro that will write in column G all the conflicting sites.

Please Login or Register  to view this content.

See attached workbook for a working example.
I'm using Sheet2 as a calculating facility. There are named ranges in there. make sure you don't erase them.

as i was testing out the functionality of the script i found that the distance the macro is outputing is wrong by about 4 miles

for exmple: 32.248723 -93.2114922038100-6.94 / 2038100-6.94

32.219155 -93.19921 2038100-6.94 /

but the actual distance is 2.17 miles approximately not 6.94 as reported

could it be that the script is calculating to fast and the captured mile is a distance from another locations?

sorry, here is the modified macro.
I was converting the degrees into radian but your formula already takes care of this.

Please Login or Register  to view this content.