Organize data by the number of decimal places

[tscottharp]

Hello,

I am working with longitude/latitude for crime data and I am having trouble cleaning it. In order to be useful for my purpose, the long/lat must have at least 3 decimal places otherwise the location given would not be precise enough. I have attached a screen shot for an example of what I am working with. I am trying to either remove data with less than 3 specified decimal places, or organize the rows by the number of decimal places rather than the value.



Thanks!
-Tyler

 

[IDidNotDoIt]

Hi, tscottharp!

Despite of the fact that there exist coordinates even with no decimal places (sites at exact degrees of either latitude or longitude), you could try at H2:
=IF(AND(ROUND(F2,3)=F2, ROUND(G2,3)=G2),"N","Y")
Then filter by H column.
Regards!

 

[tscottharp]

Hi, tscottharp!

Despite of the fact that there exist coordinates even with no decimal places (sites at exact degrees of either latitude or longitude), you could try at H2:
=IF(AND(ROUND(F2,3)=F2, ROUND(G2,3)=G2),"Y","N")
Then filter by H column.
Regards!

Thanks for your help! It's a good point about the exact longitude latitude, and I'll keep that in mind while working with my data. I was just running into the issue where some of the values were truncated from my original source's crime locations such that they fell outside the boundaries of Detroit, where they where supposed to have taken place.

Again, thank you for your quick response!

 

[Peter Bartholomew]

You could try plotting the Lat/Long coords on a scatter chart. Maybe each area as a separate series so that they show with distinct markers. Another series with the central point of each area labelled would make looking for mis-positioned reports easier. If the point lies exactly on the vertex of a coordinate grid I might be worth going back in records to pick up street names etc.