Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Crime Analyst needs help with co-ordinates
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Installed Analyst Toolpak but not working for files used at work? | Excel Worksheet Functions |