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
zone	lat	long	dist (km)	avg lat	avg long	dist vs avg
A
A
A
B
B
B

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]45.44033[/TD]
[TD="align: right"]10.99706[/TD]
[TD="align: right"]0.55[/TD]
[TD="align: right"]45.43926[/TD]
[TD="align: right"]11.00492[/TD]
[TD="align: right"]0.62[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]45.43764[/TD]
[TD="align: right"]10.99111[/TD]
[TD="align: right"]0.74[/TD]
[TD="align: right"]45.4406[/TD]
[TD="align: right"]11.0079[/TD]
[TD="align: right"]1.35[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=969696]#969696[/URL] "]C[/TD]
[TD="align: right"]45.4427[/TD]
[TD="align: right"]10.99718[/TD]
[TD="align: right"]1.69[/TD]
[TD="align: right"]45.43366[/TD]
[TD="align: right"]11.04389[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=969696]#969696[/URL] , align: right"]3.78[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]45.44087[/TD]
[TD="align: right"]11.01873[/TD]
[TD="align: right"]3.81[/TD]
[TD="align: right"]45.43899[/TD]
[TD="align: right"]10.99409[/TD]
[TD="align: right"]1.93[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: right"]45.44352[/TD]
[TD="align: right"]10.96999[/TD]
[TD="align: right"]3.02[/TD]
[TD="align: right"]45.43309[/TD]
[TD="align: right"]10.98706[/TD]
[TD="align: right"]1.77[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="align: right"]45.41874[/TD]
[TD="align: right"]10.98581[/TD]
[TD="align: right"]4.83[/TD]
[TD="align: right"]45.44548[/TD]
[TD="align: right"]10.97915[/TD]
[TD="align: right"]3.02[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=969696]#969696[/URL] "]C[/TD]
[TD="align: right"]45.43366[/TD]
[TD="align: right"]11.04389[/TD]
[TD="align: right"]4.60[/TD]
[TD="align: right"]45.4427[/TD]
[TD="align: right"]10.99718[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=969696]#969696[/URL] , align: right"]3.78[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="align: right"]45.44743[/TD]
[TD="align: right"]10.98831[/TD]

[TD="align: right"]45.43113[/TD]
[TD="align: right"]10.9779[/TD]
[TD="align: right"]1.99[/TD]

</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