Mapping Hacks

40

|

Chapter 1, Mapping Your Life

#11 How Far? How Fast? Geo-Enabling Your Spreadsheet

HACK

There is a trick to SplitMinSecToDeg( ) that comes up when you try to do the

equivalent function manually. You should be able to convert from DMS to

decimal degrees with this formula:

=deg_clean+min_clean/60+sec_clean/3600

But wait! That shows the original longitude as being –117° 55’ 8.44”, while

the formula then comes up with –116.08099°. What is going on? This is a

consequence of using positive and negative numbers to depict latitudes

north and south of the equator and longitudes east and west of the prime

meridian. The problem is that Disneyland is not at negative 117 degrees of

longitude; it is at positive 117 degrees west of the prime meridian. There are

many ways to abuse math in Excel, but here is one fix:

=SIGN(S11)*(ABS(S11)+T11/60+U11/3600)

After this correction, we have the right latitude and longitude! The correc-

tion should also be done on latitude, except that because Disneyland is in

California, I need not worry about the Southern Hemisphere in this particu-

lar example.

I like to develop spreadsheets one step at a time, rather then creating one

monstrous, impossible-to-understand formula. Since there is also some-

thing to be said for the all-in-one approach, here are the same formulas

rolled together at once, which are stored in the sheet labeled Disney Way-

points-all in one:

=SplitMinSecToDeg(LEFT(D11,FIND("¯",D11)-1)+0,LEFT(E11,FIND("'",E11)- ...