Forum Discussion

gokoe's avatar
gokoe
Copper Contributor
Jan 29, 2020

Finding clusters of points on a map

I have a long list of latitudes and longitudes constituting a list of points on a map. My goal is to identify, for each point, how many other points in the list are within 2 miles, and return the result in one cell. I'm guessing this will involve a countif and some kind of array function that I'm unfamiliar with. I already have the following formula to calculate distance (in miles) between two latitude/longitude points:

 

=ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-Lat2)) *COS(RADIANS(Long1-Long2))) *3958.756

 

Any suggestions?

Formulas and Functions

1 Reply

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 29, 2020

    gokoe 

    Let me illustrate on very simple sample.

     In column C we have some point and in D calculate for each point how many other point have value which differ from this one on not more than one. Formula could be

    =SUMPRODUCT(--(ABS(C3-$C$3:$C$9)<=1))-1

    and drag it down.

     

    Perhaps similar works in your case, but it's better to play with actual data.

Resources