Crime Analyst needs help with co-ordinates

[sevil_gp]

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

What I'm after is a Macro to produce additional columns with th
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 northin
columns. I've had a go myself with no success. I've also looked on th
web at some epidemiology sites and similar but can find nothing of use

 

[Faz]

Do you know what Cartesian equations are? They're equations mainly to d
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 an
you want to know if something is within 1000 miles of the centra
point.
Assuming the certain something you are searching for is 70 East and 40
North, you need to know if this reference is within the 1000 mile radiu
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 mil
radius",IF(POWER((100-70),2)+POWER((500-400),2)=1000,"On the 1000 mil
radius","Within the 1000 mile radius"))

This equation will tell you if any point is within/outside any othe
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,"Outsid
circumference",IF(POWER((A1-A2),2)+POWER((A3-A4),2)=A5,"O
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

 

[Dave Peterson]

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.