Mapping Hacks

42

|

Chapter 1, Mapping Your Life

#12 Create a Distance Grid in Excel

HACK

In those 23 seconds we went 15 feet. So to calculate the speed in miles per

hour, we need to convert 23 seconds to hours, and 15 feet to miles.

This checks that the elapsed time is not zero and then converts feet in D6 to

miles, and seconds in cell F6 to hours:

=IF(F6<>0,(D6/5280)/(F6/3600),0)

So 15 feet in 23 seconds is about 0.44 mph. Way down in row 104, we went

43.43 mph. Quite the thrill park! (Hey, when riding with a five and six year

old, 43.43 mph is pretty darn fast!)

Geo-Enabling Other Spreadsheets

Once you have played with the sample sheet, you’ll want to install the mac-

ros as a plug-in, so you can use the geospatial functions in your own spread-

sheets. You can get a copy of the VBA functions from http://www.

mappinghacks.com/data/spreadsheet/geofunc.xla.

Download the file and place it in Excel, select Tools

➝ Add-ins, and click

Select. Navigate to your download directory, select the file geofunc.xla, and

click Open. Congratulations, you now have a geo-enabled spreadsheet!

The original version of the geofunc.xla library lives at http://nmml.afsc.noaa.

gov/Software/ExcelGeoFunctions/excelgeofunc.htm. There is also more infor-

mation about the original functions in the library at that site.

Loading geodata into a spreadsheet extends the great tools we are accus-

tomed to in numerical analysis to the problems of acquisition, ...