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 24
places, in longitude and latitude (in decimal degrees). We will cal
this target sheet.

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

Goal 1 - Calculate the great circle distance between a set o
coordinates on the target sheet with the coordinates on the referenc
sheet. I am using the spherical law of cosines to do this, and i a
success when calculating one set of coordinates (target sheet) agians
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 formul
=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 targe
sheet agianst the ENTIRE set of coordinates of the reference sheet an
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 t
create a formula that covers all these requirments i get errors and th
syntax for such a large formula hurts my brain!

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

ge

 

[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.