6diegodiego9
Board Regular
- Joined
- Jan 9, 2018
- Messages
- 80
- Office Version
- 2016
- Platform
- Windows
I have a list of coordinates (columns B and C) of agencies (rows) that are associated to a zone (column A).
I attached a screenshot of a reduced list as example.
In cell D2 of the example I put a formula that calculates the distance between coordinates in row 2 and row 3:
=6371*((2*ASIN(SQRT((SIN((RADIANS(B2)-RADIANS(B3))/2)^2)+COS(RADIANS(B2))*COS(RADIANS(B3))*(SIN((RADIANS(C2)-RADIANS(C3))/2)^2)))))
I'm now asked to fill column E with the average distance between the coordinates of current row and the coordinates of each other row of the same zone (column A), on a real case file that has thousands of rows and 10s of zones...
How would you solve this problem?
I attached a screenshot of a reduced list as example.
In cell D2 of the example I put a formula that calculates the distance between coordinates in row 2 and row 3:
=6371*((2*ASIN(SQRT((SIN((RADIANS(B2)-RADIANS(B3))/2)^2)+COS(RADIANS(B2))*COS(RADIANS(B3))*(SIN((RADIANS(C2)-RADIANS(C3))/2)^2)))))
I'm now asked to fill column E with the average distance between the coordinates of current row and the coordinates of each other row of the same zone (column A), on a real case file that has thousands of rows and 10s of zones...
How would you solve this problem?