Excel 3d Surface Tool

Fantastic video Saltash School Excel Surface Presentation (caution- opens in youtube, so you may need to be logged in on your school RBC to show this).

The video talks about using a transparency and overlay – I like the idea of doing part of this job away from a computer.

If anyone wants an interesting challenge – plot this one! http://maps.bing.co.uk/maps/?v=2&cp=53.86248115696801~-1.9574975967407226&lvl=16&sty=s (this links to an OS map in Bing).

For a more interesting challenge – walk back UP the hill AFTER being in here for too many hours. Strange how their map just shows the pub being down any old road!

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.   

Read more of this post

Google Maps and Data Handling

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  

Sign In

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

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

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)

We're on a path ..

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.

Follow roads off then on

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

Note show roads and popup

To give –  

Is it really that steep?

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

Excel - not happy..

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)  

The info in notepad

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  

Get the data

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!)  

Check the data ..

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

Tidying up the input

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

First data slab

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

If you doubt your sanity..

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

Add data AFTER existing

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  

I earned that beer

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