Data Handling and Google Maps

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..     

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.    

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.