Mapping Hacks

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

Chapter 1, Mapping Your Life

|

41

HACK

all track points as part of the same line. The center cannot hold, and ran-

dom straight lines blur the clarity of the trip!

The sheet Disney Track shows a sample tracklog that has been imported

into Excel and processed to a clean display of lat/long (Figure 1-22).

Calculating Cumulative Distance and Speed

With latitude, longitude, and the new-segment flag in columns A, B, and C,

we can calculate distance. We must first look at the new-segment flag. If this

is a new segment, indicated by

[1];, then we should skip calculating the dis-

tance. Otherwise the distance in feet is given with the

posDist( ) function

multiplied by the number of feet in a nautical mile.

=IF(C6="[0];", posdist(A5,B5,A6,B6)*$D$3,0)

And the cumulative trip distance:

=SUM($D$5:D6)

The Garnix datestamp is well suited for Excel. You can subtract the times-

tamp of the previous track point from the current timestamp to get the

elapsed time. To make it more interesting, you need to check the new-seg-

ment flag and ignore the time between segments (unless you are calculating

a time stopped). You also need to multiply the elapsed time by the number

of seconds in one day in order to get the elapsed time in seconds. Assuming

the following timestamps are in cells P5 and P6, then:

3/13/2004 5:44:30 AM

3/13/2004 5:44:53 AM

=IF(C6="[0];", (P6-P5)*86400,0)