Have adapted an array formula from a previous thread - "Need to compare Latitude & Longitude coordinates to find closest office"
The formula is in C2 and picks up Lon and Lat from A1 & A2 and locates nearest from H2:J7:
I calculate the result to be 16.09km from the Waypoint however when i tested this i found it to be incorrect as closet in this case is 6394 (Red) at 15.99km
Can anyone see what is wrong within this formula?
[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD="width: 70"]B[/TD]
[TD="width: 81"]C[/TD]
[TD="width: 70"]D[/TD]
[TD="width: 70"]E[/TD]
[TD="width: 70"]F[/TD]
[/TR]
[TR]
[TD]Lon[/TD]
[TD]Lat[/TD]
[TD]Nearest[/TD]
[TD]Lon[/TD]
[TD]Lat[/TD]
[TD]Distance[/TD]
[/TR]
[TR]
[TD]3.53[/TD]
[TD]49.7366666667[/TD]
[TD="bgcolor: #FFC000"]6402[/TD]
[TD]3.3106111111[/TD]
[TD]49.708[/TD]
[TD]16.0[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]H[/TD]
[TD="width: 85"]I[/TD]
[TD="width: 87"]J[/TD]
[TD="width: 101"]K[/TD]
[/TR]
[TR]
[TD]Time[/TD]
[TD]Lon[/TD]
[TD]Lat[/TD]
[TD]Distance From WayPoint[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFFFFF"]6388[/TD]
[TD="bgcolor: #FFFFFF"]3.3063611111[/TD]
[TD="bgcolor: #FFFFFF"]49.73825[/TD]
[TD="bgcolor: #FFFFFF"]16.0726250391[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFFFFF"]6389.5[/TD]
[TD="bgcolor: #FFFFFF"]3.30675[/TD]
[TD="bgcolor: #FFFFFF"]49.7353055556[/TD]
[TD="bgcolor: #FFFFFF"]16.0449141001[/TD]
[/TR]
[TR]
[TD="bgcolor: #FF3333"]6394[/TD]
[TD="bgcolor: #FF3333"]3.3081111111[/TD]
[TD="bgcolor: #FF3333"]49.7253333333[/TD]
[TD="bgcolor: #FF3333"]15.997733609[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFFFFF"]6394.5[/TD]
[TD="bgcolor: #FFFFFF"]3.3083055556[/TD]
[TD="bgcolor: #FFFFFF"]49.7238611111[/TD]
[TD="bgcolor: #FFFFFF"]15.9977815714[/TD]
[/TR]
[TR]
[TD="bgcolor: #FF9900"]6402[/TD]
[TD="bgcolor: #FF9900"]3.3106111111[/TD]
[TD="bgcolor: #FF9900"]49.708[/TD]
[TD="bgcolor: #FF9900"]16.0900565802[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFFFFF"]6402.5[/TD]
[TD="bgcolor: #FFFFFF"]3.3108055556[/TD]
[TD="bgcolor: #FFFFFF"]49.7065277778[/TD]
[TD="bgcolor: #FFFFFF"]16.1098474752[/TD]
[/TR]
</tbody>[/TABLE]
The formula is in C2 and picks up Lon and Lat from A1 & A2 and locates nearest from H2:J7:
Code:
{=INDEX($H$2:$H$7;MATCH(SMALL((ABS($A2-$I$2:$I$7)^2+ABS($B2-$J$2:$J$7)^2)^(0.5);1);(ABS($A2-$I$2:$I$7)^2+ABS($B2-$J$2:$J$7)^2)^(0.5);0))}
I calculate the result to be 16.09km from the Waypoint however when i tested this i found it to be incorrect as closet in this case is 6394 (Red) at 15.99km
Can anyone see what is wrong within this formula?
[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD="width: 70"]B[/TD]
[TD="width: 81"]C[/TD]
[TD="width: 70"]D[/TD]
[TD="width: 70"]E[/TD]
[TD="width: 70"]F[/TD]
[/TR]
[TR]
[TD]Lon[/TD]
[TD]Lat[/TD]
[TD]Nearest[/TD]
[TD]Lon[/TD]
[TD]Lat[/TD]
[TD]Distance[/TD]
[/TR]
[TR]
[TD]3.53[/TD]
[TD]49.7366666667[/TD]
[TD="bgcolor: #FFC000"]6402[/TD]
[TD]3.3106111111[/TD]
[TD]49.708[/TD]
[TD]16.0[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]H[/TD]
[TD="width: 85"]I[/TD]
[TD="width: 87"]J[/TD]
[TD="width: 101"]K[/TD]
[/TR]
[TR]
[TD]Time[/TD]
[TD]Lon[/TD]
[TD]Lat[/TD]
[TD]Distance From WayPoint[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFFFFF"]6388[/TD]
[TD="bgcolor: #FFFFFF"]3.3063611111[/TD]
[TD="bgcolor: #FFFFFF"]49.73825[/TD]
[TD="bgcolor: #FFFFFF"]16.0726250391[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFFFFF"]6389.5[/TD]
[TD="bgcolor: #FFFFFF"]3.30675[/TD]
[TD="bgcolor: #FFFFFF"]49.7353055556[/TD]
[TD="bgcolor: #FFFFFF"]16.0449141001[/TD]
[/TR]
[TR]
[TD="bgcolor: #FF3333"]6394[/TD]
[TD="bgcolor: #FF3333"]3.3081111111[/TD]
[TD="bgcolor: #FF3333"]49.7253333333[/TD]
[TD="bgcolor: #FF3333"]15.997733609[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFFFFF"]6394.5[/TD]
[TD="bgcolor: #FFFFFF"]3.3083055556[/TD]
[TD="bgcolor: #FFFFFF"]49.7238611111[/TD]
[TD="bgcolor: #FFFFFF"]15.9977815714[/TD]
[/TR]
[TR]
[TD="bgcolor: #FF9900"]6402[/TD]
[TD="bgcolor: #FF9900"]3.3106111111[/TD]
[TD="bgcolor: #FF9900"]49.708[/TD]
[TD="bgcolor: #FF9900"]16.0900565802[/TD]
[/TR]
[TR]
[TD="bgcolor: #FFFFFF"]6402.5[/TD]
[TD="bgcolor: #FFFFFF"]3.3108055556[/TD]
[TD="bgcolor: #FFFFFF"]49.7065277778[/TD]
[TD="bgcolor: #FFFFFF"]16.1098474752[/TD]
[/TR]
</tbody>[/TABLE]