I have a worksheet of property latitude and longitudes along with another sheet of the county centroid latitude and
longitude. Along with each property record is a price field that will also factor in the results.
county, I have to calculate the distance from the county centroid to each property and return the prices for the top 5
closest counties (where the distance is greater than 0). From there, I need to calculate a median on the prices.
I am using this formula for calculating the distance between two lats and lons:
=ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-Lat2))
In this example, the lat1, lon1 values would be coming from the centroid of the county, and the lat2, lon2 values would be
the property (but I need to basically run this calculation on a range of properties).