formula for putting points into groups
 

Column A is a list of up to 20 locations. Column B is the 'X'
coordinate, column C is the 'Y' coordinate.

The distance between any 2 points is figured with the ol'
squareroot("X" squared + "Y" squared).

Visually, if I graphed all those points, you may see groupings of
points, ie. any number of groups of points that are within a certain
distance of each other. There could possibly be only 1 group if all
the points were close, or 20 distinct groups if all 20 locations were
spread apart.

I would like Excel to be able to assign a group number to those groups,
based on a distance variable put in by the user. So if the user put in
a distance of 50 miles for example, Excel would go down my list of up
to 20 locations, and in column D, number each group of locations.

I am finding it easy to find out which locations are within that
distance variable of any location, but take the following example:

3 locations, all aligned vertically in a line: location 2 is 40 miles
from location 1, and location 3 is 40 miles from location 2, like in
the following diagram:

1

2

3

If the user enters 50 miles, it should place all 3 locations within the
same group since 1 and 2 are within the given distance, and 2 and 3 are
within the given distance, and location 2 exists in both.

Hard to explain, but hopefully I got my point across. Any help would
be greatly appreciated.


--
grime
------------------------------------------------------------------------
grime's Profile: http://www.excelforum.com/member.php...o&userid=19227
View this thread: http://www.excelforum.com/showthread...hreadid=475793