Minimum Distance Calculation using Array and Geographical Coordinates
 

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

Minimum Distance Calculation using Array and Geographical Coordinates
 

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.


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=550152