You are here:Home Resources ICT Running

Running

Target Setting for the Deluded?

So – I’ve set myself a half marathon target time of 1hr 30mins – a mere 15 minutes quicker than I’ve achieved in an awful lot of years.

This post is also going to showcase a neat trick by Windows Live Writer – make a page from posts.

But – the page will be developed like a blog post – with the newest stuff at the top

20th July

A little jaunt from Brougton Astley to Croft Hill – return! Here’s the google map profiles

52.528546° N 1.214333° W 315 ft

312° 1.2 miles1 total 52.538727° N 1.232958° W 292 ft
320° 0.7 miles 2 total 52.546191° N 1.243343° W 272 ft
346° 1.0 miles 3 total 52.559134° N 1.248751° W 256 ft
345° 0.1 miles 3 total 52.56023° N 1.249223° W 269 ft
307° 0.1 miles 3 total 52.560882° N 1.250639° W 259 ft
358° 0.3 miles 3 total 52.565291° N 1.250896° W 315 ft
77° 0.1 miles 3 total 52.565525° N 1.24918° W 348 ft
339° 0.1 miles 4 total 52.567091° N 1.250167° W 299 ft
51° 0.3 miles 4 total 52.570247° N 1.243773° W 243 ft
184° 0.1 miles 4 total 52.569256° N 1.243901° W 269 ft
254° 0.0 miles 4 total 52.569125° N 1.244631° W 266 ft
275° 0.0 miles 4 total 52.569177° N 1.245575° W259 ft

The stuff in italics was repeated for the run back. Interesting looking at the 0.0 miles entry here!

imageSo – okay – no miles!
Clearly this little jaunt had no bearing whatsoever on the time!

According to Googlemaps this set of steps simply doesn’t exist.

Total 8 miles plus a few zeroes for good measure 1hr 35 – ooh, very slow as road running goes.

But that hill profile is true – the hill is much higher and steeper than the steps. In my book, that’s a good excuse.

Was it worth it? Have a look – you may need silverlight.  View from a hill

Photo © www.sarahmcsharry.co.uk Photosynth © Me :)

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
                              Below this block is from previous Blog POSTS
+++++++++++++++++++++++++++++

Part 2 – The Hard Bit (Handling the Data)

In part 1 I very deliberately glossed over the work required to make sense of the data presented by the Google App and how it worked inside Excel.

The value for handling the data from this app depends on the age group and ICT expectations of your pupils.

If you’re looking at this as a GCSE IT project, then ask your pupils to code the sheet with a VB macro and please can I have a copy?

If – more likely – you’re looking at this and saying ‘I don’t care how awkward the data is that you start with – please give it to me so I can easily use in a spreadsheet and make a graph without too much effort’ – then read on.

What you should have at the end of this post is a working spreadsheet you can use again and again and (hopefully) an understanding of some of the powerful string manipulation tools in Excel. I’m using 2007 but I think most of the ideas shown work in Excel 2003 and probably OpenOffice.

Start at the very beginning

This was the data presented to us from the Google App – we could simply say – the 1st line contains the height at the start, the 2nd line the distance to first point, etc. So – just copy and paste it – job done move on to the spreadsheeting and graphing stuff.

But we don’t want to do it like that!

We want to make it complicated (ish).Let’s see what Excel can take apart – so what do we have going in?

The distance is found starting 2 spaces after the W. So use Search to find the W

Our number starts 2 spaces beyond there – consistently (that’s the key word) – so let’s record that.

The end of our number is 2 spaces from the right hand side of the whole length of the string.

Now – lets use the info we know and extract the actual number with the mid function

Et voila! The height! (I don’t know the French for that :( )

Now -  let’s use a similar trick to find the distance. Notice that the app presents the leg distance (1.8km) and the rounded total (4). The totals are actually no use for this task. We’re looking for the letters km and the degree sign and we’ll pull the distance in a similar fashion to the height..

Now – ignore column H – that will put all our results in column I.

In order to get the screen grabs above I used the hide feature on rows 1 and 2. To unhide the first rows (or columns) in a sheet is a bit numb – and unfortunately I can’t get a screen grab – but move the cursor to the row header (that’s the label in the row) – then when the cursor changes to a small parallel – I’ll try to get a video sorted! So – now we have one column that – reading down shows -
height

leg distance
height

leg distance
height

So we need the first leg distance to be zero – cell I1 is a blank – that’ll do. So now we have it so that cells I1 and I2 then I3 and I4 etc have the info we need.

We would like our data in a continuous block, rather than with spare lines. So, in the columns dropping down from cells j2 and k2 calculates the cell references for the cells with the data.

The formula for cell j2 gives us the cell label for the height
The formula for cell k2 gives us the label for the distance
Copy the cells j2 and k2 down as far as you need
 
How to use the cell labels – the rarely used indirect
Indirect says – look in the cell specified – then give me the value from that cell reference – a cool tool!
So with L2 selected – we can see that we are looking back indirectly for our data. Copy L2 to M2 then drag them down .. now we have the block we need – contiguous (not a spelling mistake).
If you look at the list you’ll see 114 0 114 – I did a figure 8 and recorded 2 seperate sessions with the Google app. We’ll panic about this one shortly.
 
Here’s the scenario – I’d like to use a multi worksheet workbook, where the first worksheet is the main info page. the second page is the route (that is the data about which we will record data) – and I’ll record the times in sheets 3 onwards are going to be the places where I record the times.
Make the results sheet
At the bottom of the sheet, right click the sheet tab – currently showing Sheet1 and select rename on the menu. Rename it to routedata.
Rightclick it again and click insert – then select worksheet.
This ‘should’ put you a new sheet to the left called Sheet1 – right click this and rename to output

Then rename the other sheet to results1 (no spaces between the words).

The Home Run (kind of!)

In the ‘output sheet’ we’ll link to the ‘routedata’ and the results sheets. It’s the links to te routedata that will allow us to ignore the stop and restart.

In ‘output’ go to cell B2 (we’ll use column A for point names and row 1 for headings) and type = then skip to the ‘routedata’ sheet, click in cell L2 and click the enter key.

If you copy b2 to c2 then drag-copy b2:c2 downwards you’ll see we get this problem.

Simply copy down to the last good cell, then start again from the restart (cell b7). Then in column 1 enter the point names on your run – the across row 1 put in the headings you’re interested in.
 
So – from here ..
We now go to the results tab and enter the individual data – start by simply referring to the output locations in the results sheet – like so -
 
Then simply drag the formula down, then enter the dates in the first row, and the times below – remember to put zero for the start!
Now to make some pretty pictures.
What do we want to see with the data? How about the detail for any one run and a running summary of the effect of the times on a half marathon (20km) forecast?
The ascii code for A is 65 – so the char() result for 65 is A
So if we use an entry number request for a run number, validate it then add it to A – we’ll get our column required.
So – bit more ambitious on the code here – here’s D2 (we’ll hide this column later)
And pop into column e and use the indirect again -
Let’s add some extra headings, do a bit of tidying up, then (as they said on t’other side of the pond) ‘do the math’.
The cumulative distance is the made up as follows -
the equation for cell F3 is =F2+C3
the equation for cell F4 is = F3+C4 etc..
Make sure you leave cell G2 and H2 blank – the results amy be as good as this if you don’t.
Average minutes per leg is the leg time/leg distance
Target half marathon time is (21.1/cumulative dist)*time
Then slect all column F G and H and force it to 1 decimal place – otherwise you may find you’re trying to improve on that final microsecond.
And just do a bit of tidying up – eg centring the data and hide column D (Don’t delete it!)
Now – graph the math .. that’s going to be part 3!!

 

Part 1 – The Google Maps Bit

(Finally got my finger out – here’s part 2).
I’ve been inspired by some of the stuff that Ollie Bray has been doing with Google Maps, and I’ve been meaning to put the following together for a while. Can the tools with Google maps lend themselves to other curriculum tasks – like data handling.

Before you can do any of the following you’ll need a gmail or googlemail email account. If you’re trying any of this from inside EMBC or some other RBCs you’ll need to log in as an adult to get the correct filtering.

Start at maps.google.co.uk and – in the location window – the one on the right – make sure you’re somewhere near the right location.

Let the fun begin..

Firstly make sure you’re logged in to your googlemail / gmail account

Then – to get your google Apps in to play – select My Maps

If you haven’t used MyMaps before, have a browse to see what else is there

We’re looking for Path Profiler – so find that one and click it and ‘Add it to Maps’. You’ll now see Path Profiler in your ‘My Maps’.

Click on path profiler and select the options.

In this case, I’ll pick up a training run (yes, even old people can try half marathons!). This run actually starts with a path through Bradgate Park to another car park – so to start with untick the ‘follow roads’ option. (A photosynth of the park is here)

Click on the first point on the run, then the second which is the end of the path. Then click on the ‘follow roads’ as we’re now back on roads. Notice the difference when you put the third point in  – the profiler follows the road.

Keep clicking on points until you’ve reached the end of the lap, then click on the popup tab on the mymaps window.

To give -

Now the tricky bit starts – we want to play with the data, so CAREFULLY select the data lines and press ctrl-c to copy it to the buffer.

If you try to paste it into Excel, you’re in for a nightmare ..

The challenge is that the data presented from Google Maps is in 2 lines and is not delimited in any way. We could make a macro to mess this about, but let’s try a way that doesn’t need macros..

Start by pasting the copied text into Notepad, to see what we have – (I also ran the file through debug, but there are no special characters to work with)

Now – from notepad – save this as a txt file, then go back into Excel on a new sheet. (Excel 2007) click on the data tab and from text

Then select the file when prompted.
Note that Excel struggles here – it thinks the data is on a fixed filed length – it isn’t – the data is separated by spaces. (Tip – look at the letter W – it doesn’t work!)

So – select the delimited option, then select space and ‘treat consecutive delimiters as one’

The next option specifies the start point of where your data is loaded to in to your sheet

Now that was a pretty tough run – but not long enough to give me a clue on stamina, resilience or – more importantly – stupidity! So – let’s make a figure 8..

Repeat the previous procedure – to get the data in via notepad – but this time be careful of the location where the data will go

When the data is in Excel, you have some serious tidying up to do. On the run I recorded my times, and Excel produced this – after bits of tidying up and use of chart options

This sheet shows a few things.

I am officially stupid
I am officially slow
I ran faster downhill than uphill
It took much longer going on the park path to second car park second time around because I was slightly cream crackered and the park was much busier

++++++++++++++++++++++++++++++++++