One more take...

I would assume that since you're looking at local statistics that you could just
assume that the earth is flat--and use the distance formula to find the distance
between any two points.

And if you plot your points on that flat map, pick out any arbitrary x axis and
y axis and measure the distance to the right/left and up/down and use them for
your east and north figures. (Measure in kilometers.)


Say you have two points (a,b) and (c,d)
(east, north) or (north, east)
<it doesn't matter which order as long as you're consistent

The distance between these two points is:

distance = sqrt(((a-c)^2) + ((b-d)^2))
(Essentially the Pythagorean theorem)

So I put some test data on a worksheet:

I put the Crime number in Column A.
I put Easting in column B
I put Northing in column C
I put 10, 50, 100, 1000 in D1:G1
Then in A2:C10, I put my data (no empty cells!)

In D2, I put this formula:
=SUMPRODUCT(--(SQRT((($B$2:$B$10-$B2)^2)+(($C$2:$C$10-$C2)^2))<=D$1))-1

Change those $b$10, $c$10 to use the last row with data.

Then drag across from D2 through G2
and drag down the rows.

==========
Now my question...

What do you mean by Northing and Easting. Did you use longitude and latitude?

If you did then this won't work as written.

But Chip Pearson has some info about working with longitude/latitude at:
http://www.cpearson.com/excel/latlong.htm

But these formulas will get pretty ugly pretty quickly.


sevil_gp wrote:

I need an Excel macro to find clusters within a list of 6 figure easting
and northing co-ordinates. I can readily export a three column spread
sheet with the column headings:
'crime number'
'easting'
'northing'

What I'm after is a Macro to produce additional columns with the
headings:

'crimes within 10 metres'
'crimes within 50 metres'
'crimes within 100 metres'
'crimes within 1 kilometre'

These can obviously be calculated from the easting and northing
columns. I've had a go myself with no success. I've also looked on the
web at some epidemiology sites and similar but can find nothing of use.

--
sevil_gp
------------------------------------------------------------------------
sevil_gp's Profile: http://www.excelforum.com/member.php...o&userid=27905
View this thread: http://www.excelforum.com/showthread...hreadid=474044

--

Dave Peterson