simple problem - difficult solution(?): average distance between coordinates in same zone

[6diegodiego9]

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?

 

[JustynaMK]

Hi Diego,

How about simply using in cell E2 & below:
=AVERAGEIF(A:A,A2,D:D)

It will present the average distance for each zone.

 

[Andrew Fergus]

Could you take the average of the other A lat's and long's and then use your existing formula to calculate the distance between the current row and the averages? Would that work mathematically? The alternative is to calculate all of the distances and then average that.

 

[Andrew Fergus]

i was thinking something like this:


Sheet10

	A	B	C	D	E	F	G
1	zone	lat	long	dist (km)	avg lat	avg long	dist vs avg
2	A	45.44033	10.99706	0.55	45.43926	11.00492	0.62
3	A	45.43764	10.99111	0.74	45.4406	11.0079	1.35
4	C	45.4427	10.99718	1.69	45.43366	11.04389	3.78
5	A	45.44087	11.01873	3.81	45.43899	10.99409	1.93
6	B	45.44352	10.96999	3.02	45.43309	10.98706	1.77
7	B	45.41874	10.98581	4.83	45.44548	10.97915	3.02
8	C	45.43366	11.04389	4.60	45.4427	10.99718	3.78
9	B	45.44743	10.98831		45.43113	10.9779	1.99

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D2 =6371*((2*ASIN(SQRT((SIN((RADIANS(B2)-RADIANS(B3))/2)^2)+COS(RADIANS(B2))*COS(RADIANS(B3))*(SIN((RADIANS(C2)-RADIANS(C3))/2)^2))))) E2 =(SUMIF(A:A,A2,B:B)-B2)/(COUNTIF(A:A,A2)-1) F2 =(SUMIF(A:A,A2,C:C)-C2)/(COUNTIF(A:A,A2)-1) G2 =6371*((2*ASIN(SQRT((SIN((RADIANS(B2)-RADIANS(E2))/2)^2)+COS(RADIANS(B2))*COS(RADIANS(E2))*(SIN((RADIANS(C2)-RADIANS(F2))/2)^2))))) D3 =6371*((2*ASIN(SQRT((SIN((RADIANS(B3)-RADIANS(B4))/2)^2)+COS(RADIANS(B3))*COS(RADIANS(B4))*(SIN((RADIANS(C3)-RADIANS(C4))/2)^2))))) E3 =(SUMIF(A:A,A3,B:B)-B3)/(COUNTIF(A:A,A3)-1) F3 =(SUMIF(A:A,A3,C:C)-C3)/(COUNTIF(A:A,A3)-1) G3 =6371*((2*ASIN(SQRT((SIN((RADIANS(B3)-RADIANS(E3))/2)^2)+COS(RADIANS(B3))*COS(RADIANS(E3))*(SIN((RADIANS(C3)-RADIANS(F3))/2)^2))))) D4 =6371*((2*ASIN(SQRT((SIN((RADIANS(B4)-RADIANS(B5))/2)^2)+COS(RADIANS(B4))*COS(RADIANS(B5))*(SIN((RADIANS(C4)-RADIANS(C5))/2)^2))))) E4 =(SUMIF(A:A,A4,B:B)-B4)/(COUNTIF(A:A,A4)-1) F4 =(SUMIF(A:A,A4,C:C)-C4)/(COUNTIF(A:A,A4)-1) G4 =6371*((2*ASIN(SQRT((SIN((RADIANS(B4)-RADIANS(E4))/2)^2)+COS(RADIANS(B4))*COS(RADIANS(E4))*(SIN((RADIANS(C4)-RADIANS(F4))/2)^2))))) D5 =6371*((2*ASIN(SQRT((SIN((RADIANS(B5)-RADIANS(B6))/2)^2)+COS(RADIANS(B5))*COS(RADIANS(B6))*(SIN((RADIANS(C5)-RADIANS(C6))/2)^2))))) E5 =(SUMIF(A:A,A5,B:B)-B5)/(COUNTIF(A:A,A5)-1) F5 =(SUMIF(A:A,A5,C:C)-C5)/(COUNTIF(A:A,A5)-1) G5 =6371*((2*ASIN(SQRT((SIN((RADIANS(B5)-RADIANS(E5))/2)^2)+COS(RADIANS(B5))*COS(RADIANS(E5))*(SIN((RADIANS(C5)-RADIANS(F5))/2)^2))))) D6 =6371*((2*ASIN(SQRT((SIN((RADIANS(B6)-RADIANS(B7))/2)^2)+COS(RADIANS(B6))*COS(RADIANS(B7))*(SIN((RADIANS(C6)-RADIANS(C7))/2)^2))))) E6 =(SUMIF(A:A,A6,B:B)-B6)/(COUNTIF(A:A,A6)-1) F6 =(SUMIF(A:A,A6,C:C)-C6)/(COUNTIF(A:A,A6)-1) G6 =6371*((2*ASIN(SQRT((SIN((RADIANS(B6)-RADIANS(E6))/2)^2)+COS(RADIANS(B6))*COS(RADIANS(E6))*(SIN((RADIANS(C6)-RADIANS(F6))/2)^2))))) D7 =6371*((2*ASIN(SQRT((SIN((RADIANS(B7)-RADIANS(B8))/2)^2)+COS(RADIANS(B7))*COS(RADIANS(B8))*(SIN((RADIANS(C7)-RADIANS(C8))/2)^2))))) E7 =(SUMIF(A:A,A7,B:B)-B7)/(COUNTIF(A:A,A7)-1) F7 =(SUMIF(A:A,A7,C:C)-C7)/(COUNTIF(A:A,A7)-1) G7 =6371*((2*ASIN(SQRT((SIN((RADIANS(B7)-RADIANS(E7))/2)^2)+COS(RADIANS(B7))*COS(RADIANS(E7))*(SIN((RADIANS(C7)-RADIANS(F7))/2)^2))))) D8 =6371*((2*ASIN(SQRT((SIN((RADIANS(B8)-RADIANS(B9))/2)^2)+COS(RADIANS(B8))*COS(RADIANS(B9))*(SIN((RADIANS(C8)-RADIANS(C9))/2)^2))))) E8 =(SUMIF(A:A,A8,B:B)-B8)/(COUNTIF(A:A,A8)-1) F8 =(SUMIF(A:A,A8,C:C)-C8)/(COUNTIF(A:A,A8)-1) G8 =6371*((2*ASIN(SQRT((SIN((RADIANS(B8)-RADIANS(E8))/2)^2)+COS(RADIANS(B8))*COS(RADIANS(E8))*(SIN((RADIANS(C8)-RADIANS(F8))/2)^2))))) E9 =(SUMIF(A:A,A9,B:B)-B9)/(COUNTIF(A:A,A9)-1) F9 =(SUMIF(A:A,A9,C:C)-C9)/(COUNTIF(A:A,A9)-1) G9 =6371*((2*ASIN(SQRT((SIN((RADIANS(B9)-RADIANS(E9))/2)^2)+COS(RADIANS(B9))*COS(RADIANS(E9))*(SIN((RADIANS(C9)-RADIANS(F9))/2)^2))))) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4