H
Harlan Grove
Max wrote...
Or better still just use prose (unless you always need thing spoon-fed
in binaries, in which case newsgroups may not be the idea forums). That
way you never get burned by .xls files that contain viruses. Or are you
naive enough not to believe that's an issue?
Anyway, given an initial table of airport codes and corresponding
latitudes and longitudes, say in A1:C12, add 2 extra columns to the
table.
D1:
=RADIANS(B1)
E1:
=RADIANS(C1)
Then create a 2-way table of distances between locations. Since the
radius of the earth wouldn't vary nearly as much as the angles, ignore
it and calculate spherical distances using only the angles. Copy A1:A12
and paste into A16:A27 and paste special transpose into B15:M15. Put
="" in A28 and enter 0 in each cell in B28:M28. Enter the following
formula.
B16:
=IF($A16<>B$15,ACOS(
COS(VLOOKUP($A16,$A$1:$E$12,4,0))*COS(VLOOKUP($A16,$A$1:$E$12,5,0))
*(COS(VLOOKUP(B$15,$A$1:$E$12,4,0))*COS(VLOOKUP(B$15,$A$1:$E$12,5,0))
+SIN(VLOOKUP(B$15,$A$1:$E$12,4,0))*SIN(VLOOKUP(B$15,$A$1:$E$12,5,0)))
+SIN(VLOOKUP($A16,$A$1:$E$12,4,0))*SIN(VLOOKUP($A16,$A$1:$E$12,5,0))),0)
Fill B16 down into B17:B27, then fill B16:B27 right into C16:M27. Then
create the defined name seq referring to
=ROW(INDEX(Sheet1!$A:$A,1):INDEX(Sheet1!$A:$A,64))
This is the common setup. All of it could be converted to values. Once
the 2-way table is constructed and converted to values, there'd be no
need to keep the original table.
With the first itinerary record in G1, the following monster array
formula will give the subsequent location furthest from the initial
location.
=INDEX($B$15:$M$15,MATCH(MAX(INDEX($B$16:$M$28,MATCH(LEFT(G1,
FIND("/",G1)-1),$A$16:$A$28,0),0)*($B$15:$M$15=MID(G1&REPT("/",12),
SMALL(IF(MID("/"&G1&REPT("/",11),seq,1)="/",seq),{2;3;4;5;6;7;8;9;10;11;12;13}),
SMALL(IF(MID(G1&REPT("/",12),seq,1)="/",seq),{2;3;4;5;6;7;8;9;10;11;12;13})
-SMALL(IF(MID("/"&G1&REPT("/",11),seq,1)="/",seq),{2;3;4;5;6;7;8;9;10;11;12;13})))),
INDEX($B$16:$M$28,MATCH(LEFT(G1,FIND("/",G1)-1),$A$16:$A$28,0),0),0))
....
Or better still just use prose (unless you always need thing spoon-fed
in binaries, in which case newsgroups may not be the idea forums). That
way you never get burned by .xls files that contain viruses. Or are you
naive enough not to believe that's an issue?
Anyway, given an initial table of airport codes and corresponding
latitudes and longitudes, say in A1:C12, add 2 extra columns to the
table.
D1:
=RADIANS(B1)
E1:
=RADIANS(C1)
Then create a 2-way table of distances between locations. Since the
radius of the earth wouldn't vary nearly as much as the angles, ignore
it and calculate spherical distances using only the angles. Copy A1:A12
and paste into A16:A27 and paste special transpose into B15:M15. Put
="" in A28 and enter 0 in each cell in B28:M28. Enter the following
formula.
B16:
=IF($A16<>B$15,ACOS(
COS(VLOOKUP($A16,$A$1:$E$12,4,0))*COS(VLOOKUP($A16,$A$1:$E$12,5,0))
*(COS(VLOOKUP(B$15,$A$1:$E$12,4,0))*COS(VLOOKUP(B$15,$A$1:$E$12,5,0))
+SIN(VLOOKUP(B$15,$A$1:$E$12,4,0))*SIN(VLOOKUP(B$15,$A$1:$E$12,5,0)))
+SIN(VLOOKUP($A16,$A$1:$E$12,4,0))*SIN(VLOOKUP($A16,$A$1:$E$12,5,0))),0)
Fill B16 down into B17:B27, then fill B16:B27 right into C16:M27. Then
create the defined name seq referring to
=ROW(INDEX(Sheet1!$A:$A,1):INDEX(Sheet1!$A:$A,64))
This is the common setup. All of it could be converted to values. Once
the 2-way table is constructed and converted to values, there'd be no
need to keep the original table.
With the first itinerary record in G1, the following monster array
formula will give the subsequent location furthest from the initial
location.
=INDEX($B$15:$M$15,MATCH(MAX(INDEX($B$16:$M$28,MATCH(LEFT(G1,
FIND("/",G1)-1),$A$16:$A$28,0),0)*($B$15:$M$15=MID(G1&REPT("/",12),
SMALL(IF(MID("/"&G1&REPT("/",11),seq,1)="/",seq),{2;3;4;5;6;7;8;9;10;11;12;13}),
SMALL(IF(MID(G1&REPT("/",12),seq,1)="/",seq),{2;3;4;5;6;7;8;9;10;11;12;13})
-SMALL(IF(MID("/"&G1&REPT("/",11),seq,1)="/",seq),{2;3;4;5;6;7;8;9;10;11;12;13})))),
INDEX($B$16:$M$28,MATCH(LEFT(G1,FIND("/",G1)-1),$A$16:$A$28,0),0),0))