The worksheet created by the add-in
is illustrated below. Geographic coordinates are latitude and
longitude. The location coordinates in columns F and G are
the average values for the U.S. states. The values are from
the web site by Max
Mind.
The add-in uses Cartesian coordinates to plot maps of
the solution. The
values x and y in columns K and L hold the
Cartesian
coordinates equivalent to the Geographic coordinates in columns
F and G. The origin point for coordinates is obtained by averaging
the latitudes and longitudes in cells F64 and G64. Column y is
computed with a linear function of the latitude values. Assuming
that the earth is a perfect sphere, two points with the same
longitude but with latitudes that differ by one degree
are 111.325 kilometers or 69.172 miles apart. The value of y for
Alabama is in cell L13.
L13=111.325*(States_D_Lat-States_D_Avg_Lat)
The difference between
two points that have the same latitude is not a linear function
of the longitude. The difference expressed in KM is greatest
at the equator which has 0 latitude. As the latitude increases
in value, the points move into the northern hemisphere where
the difference per unit of longitude decreases. Similarly,
moving from the equator into the southern hemisphere decreases
the
distance between two points at the same latitude. We determine
the x column
using the great circle difference computed by the User-defined function
"GreatCircleDistance" adapted from a web page by Pearson
Software Consulting. The value of x for Alabama
is in cell K13.
K13=GreatCircleDistance( F13,States_D_Avg_Lon,F13,G13,2)
The entries for the Great Circle Distance matrix are also
computed with the GreatCircleDistance function. The matrix
is originally computed by filling in all the entries with the
function. Then the entire matrix is selected. The entries are
copied and their values are pasted (with the Paste Special
Values command). The values in the cells then are constants
and not functions. This reduces the burden of storing a large
number
of function calls in the matrix. Whenever the geographic coordinates
are changed,
click on the Compute Matrix command to regenerate
the matrix values. |
