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(l ong2−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


--
geobatman
------------------------------------------------------------------------
geobatman's Profile: http://www.excelforum.com/member.php...o&userid=35240
View this thread: http://www.excelforum.com/showthread...hreadid=550152