Minimum Distance Calculation using Array and Geographical Coordinates

[geobatman]

Hello All,

I have been working on spreadsheets and have come across a roadblock. Let me propose my problem.

I am working with a spreadsheet that contains the coordinates of 240 places, in longitude and latitude (in decimal degrees). We will call this target sheet.

I have another spreadsheet that contains the coordinates of 900 places, in longitude and latitude (in decimal degrees). We will call this reference sheet.

Goal 1 - Calculate the great circle distance between a set of coordinates on the target sheet with the coordinates on the reference sheet. I am using the spherical law of cosines to do this, and i am success when calculating one set of coordinates (target sheet) agianst a set of coordinates (reference sheet)

spherical law of cosines
d = acos(sin(lat1).sin(lat2)+cos(lat1).cos(lat2).cos(long2−long1)).R

excel formula =acos(sin(lat1*pi()/180)*sin(lat2*pi()/180)+cos(lat1*pi()/180)*cos(lat2*pi/180)*cos(lon2*pi/180-lon1*pi()/180)*6378.135

However, my ultimate goal is to:
1. Calculate the distance of a set of ONE coordinates from the target sheet agianst the ENTIRE set of coordinates of the reference sheet and returning the value having a distance of less than 50 kilometers, cannot equal zero and is the MINIMUM value of the array. When i try to create a formula that covers all these requirments i get errors and the syntax for such a large formula hurts my brain!

It is quite hard to put it into words but hopefully i've got my point across. Help would be extremely appreciated.

geo

[mrice]

I'd be tempted to go for a user defined function here which takes the coordinates from the target sheet as two of its arguments and the range containing the set on the reference sheet as a third.

The function would calculate the individual distances one by one, kepping track of the minimum. At the end of this, the min value would be tested against the 50 figure and the appropriate value returned.

[piyushau]

I have a similar problem like this.... Explained as below :

I have one excel sheet with two columns say X and Y coordinate of different locations.

1). I have to calculate the distance between the locations says distance of first location from all the other location and finding out that point which is nearest to it.

2). Thus in the first step we got a pair of points with the nearest distances now i will have to leave both these points and calculating the distance of the other points as above and again finding the pair with the shortest distance and leave this pair and so on...

3). Thus My ultimate goal is to find out the pair of locations which are closest to each other and no location should be common in any other pair.

Hope i am clear in my words.

Help will be highly appreciated!!!!

Regards,
piyush

[zbor]

hi piyushau, wellcome to the Forum.

Please read and adhere to these simple rules!

2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.