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

Do you know what Cartesian equations are? They're equations mainly to do
with circles, and this is what you need as far as i can tell.

Say a reference on the map is 100 miles East and 500 miles North and
you want to know if something is within 1000 miles of the central
point.
Assuming the certain something you are searching for is 70 East and 400
North, you need to know if this reference is within the 1000 mile radius
of the point(100,500) Let X be 100 and Y be 500 (as it is)

=IF(POWER((100-70),2)+POWER((500-400),2)1000,"Outside 1000 mile
radius",IF(POWER((100-70),2)+POWER((500-400),2)=1000,"On the 1000 mile
radius","Within the 1000 mile radius"))

This equation will tell you if any point is within/outside any other
reference point.

Sorry if this is not what you mean, but certainly sounds like it.
A general equation is this:

=IF(POWER((A1-A2),2)+POWER((A3-A4),2)A5,"Outside
circumference",IF(POWER((A1-A2),2)+POWER((A3-A4),2)=A5,"On
circumference","Within circumference"))

A1 is the distance East of the central point from the origin
A2 is the distance East of another point from the origin
A3 is the distance North of the central point from the origin
A4 is the distance North of another point from the origin
A5 is the radius that is to be measured from the the central point

With this, you shouldn't need a macro.


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

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