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?
<tbody>
</tbody>
<tbody>
</tbody>
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?
A | B | C | D | E | F |
Lon | Lat | Nearest | Lon | Lat | Distance |
3.53 | 49.7366666667 | 6402 | 3.3106111111 | 49.708 | 16.0 |
<tbody>
</tbody>
H | I | J | K |
Time | Lon | Lat | Distance From WayPoint |
6388 | 3.3063611111 | 49.73825 | 16.0726250391 |
6389.5 | 3.30675 | 49.7353055556 | 16.0449141001 |
6394 | 3.3081111111 | 49.7253333333 | 15.997733609 |
6394.5 | 3.3083055556 | 49.7238611111 | 15.9977815714 |
6402 | 3.3106111111 | 49.708 | 16.0900565802 |
6402.5 | 3.3108055556 | 49.7065277778 | 16.1098474752 |
<tbody>
</tbody>