T
Terry Pinnell
I'd appreciate some help please as I'm very rusty with function
manipulation.
The files I want to process contain GPS data representing walks (hikes).
They contain several hundred text lines ('trackpoints') showing details of
location co-ordinates and times. The times look like this:
07:57:12
07:57:48
07:58:31
etc
My aim is to identify trackpoints for which the time gap before the NEXT
trackpoint is greater than say 5 minutes. These represent where I stopped,
had lunch, stepped into a pub, etc, which can be very time consuming to
find manually.
So far my best effort is as follows, but it's very kludgy and doesn't work
properly anyway:
1. Edited the data first in my text editor so that it contained only the
times in the form above, not all the other fields.
2. Imported that file into Excel 2000, specifying a colon as the field
separator.
Hour Min Sec
----- --- ---
7 57 12
7 57 48
7 58 31
etc
3. Converted the Hrs/Mins to get Total Seconds.
4. Rounded that back to minutes, the column 'Gap (mins)'.
5. Used the formula =IF(F55>5,"Yes") in another column to decide if the
gap was longer than 5 mins.
6. Added a 'Line' column so that I will be able to identify the trackpoint
in the original file after sorting my Excel data.
SIDE-QUESTION: What is a simple way to create that succession 1, 2, 3, 4,
etc please? I can do it by typing the 1 and the 2 and then dragging those
down with my mouse - but as soon as I drag below the bottom screen edge it
becomes uncontrollable, and I get thousands of unwanted entries. Is there
a simple keystroke method? Or some way of slowing the mouse's frenzy?
So at this stage the worksheet looks like this:
Hour Min Sec Total Gap Gap Mins Line
Secs Secs Mins over 5?
----- --- --- ----- ---- ---- ------- ----
7 57 12 28632 1
7 57 48 28668 36 1 FALSE 2
7 58 31 28711 43 1 FALSE 3
7 58 56 28736 25 0 FALSE 4
etc
7. Sort that to get all those showing 'Yes' instead of FALSE at the top.
That almost worked, but there's still clearly some flaw connected with the
FIRST result:
https://dl.dropbox.com/u/4019461/Excel-Gaps-1.jpg
In any case, it's a long way short of the ideal, which would be to show
ONLY the lines matching my criterion, preferably with the all original
fields present as well. But I'd cheerfully settle for getting my
long-winded method fixed please!
manipulation.
The files I want to process contain GPS data representing walks (hikes).
They contain several hundred text lines ('trackpoints') showing details of
location co-ordinates and times. The times look like this:
07:57:12
07:57:48
07:58:31
etc
My aim is to identify trackpoints for which the time gap before the NEXT
trackpoint is greater than say 5 minutes. These represent where I stopped,
had lunch, stepped into a pub, etc, which can be very time consuming to
find manually.
So far my best effort is as follows, but it's very kludgy and doesn't work
properly anyway:
1. Edited the data first in my text editor so that it contained only the
times in the form above, not all the other fields.
2. Imported that file into Excel 2000, specifying a colon as the field
separator.
Hour Min Sec
----- --- ---
7 57 12
7 57 48
7 58 31
etc
3. Converted the Hrs/Mins to get Total Seconds.
4. Rounded that back to minutes, the column 'Gap (mins)'.
5. Used the formula =IF(F55>5,"Yes") in another column to decide if the
gap was longer than 5 mins.
6. Added a 'Line' column so that I will be able to identify the trackpoint
in the original file after sorting my Excel data.
SIDE-QUESTION: What is a simple way to create that succession 1, 2, 3, 4,
etc please? I can do it by typing the 1 and the 2 and then dragging those
down with my mouse - but as soon as I drag below the bottom screen edge it
becomes uncontrollable, and I get thousands of unwanted entries. Is there
a simple keystroke method? Or some way of slowing the mouse's frenzy?
So at this stage the worksheet looks like this:
Hour Min Sec Total Gap Gap Mins Line
Secs Secs Mins over 5?
----- --- --- ----- ---- ---- ------- ----
7 57 12 28632 1
7 57 48 28668 36 1 FALSE 2
7 58 31 28711 43 1 FALSE 3
7 58 56 28736 25 0 FALSE 4
etc
7. Sort that to get all those showing 'Yes' instead of FALSE at the top.
That almost worked, but there's still clearly some flaw connected with the
FIRST result:
https://dl.dropbox.com/u/4019461/Excel-Gaps-1.jpg
In any case, it's a long way short of the ideal, which would be to show
ONLY the lines matching my criterion, preferably with the all original
fields present as well. But I'd cheerfully settle for getting my
long-winded method fixed please!