As a complement to my series of articles about my worst training days, I’ve started a series about my best ones. I believe we can learn from both sorts. In this article, I relate an experience of running a twilight training session for a secondary school science department.
You don’t need to know about a subject in order to fire people up about a new tool technique for teaching it. For example, my knowledge of science can be written on the back of a postage stamp, and there would still be room for marginal notes. Nevertheless, when a head of Science in a secondary school phoned me one day and said:
“Terry, I’d like some IT training for my department, but we’ve covered all the usual stuff. I need you to show us something new and exciting!”,
I knew the answer straight away.
“Pivot tables.”, I said.
“What are they?”
“Tell you what, I don’t know anything about science, but how about I come along and show you and your teachers how a modelling tool called ‘pivot tables’ work, and then let you work out how to apply it?”
“It’s a deal!”
Just in case you don’t know what a pivot table is, it’s a way of organising and reorganising data in order to tease out possible relationships that might otherwise remain hidden. Let me give you a very quick demonstration.
I created a very large database, one with 23 fields and 1003 records. (Yes, I realise it’s a sad thing to be spending my evenings on, but everyone needs a hobby.)
Here’s a screenshot of some of the data.
The People Database, by Terry Freedman
As you can see, it’s somewhat impenetrable. However, by organising it into a pivot table I can obtain a much clearer picture of some of it. In the screenshot below, what I’ve done is created a pivot table from the fields Marital Status and Music Liked. I could have further classified the data into male and female too, but decided to just stick with a more general view, so I used Personality as the field by which to count the number of people in each category.
A pivot table view of some of the data, by Terry Freedman
The data shows that the divorce rate was higher for people who liked blues music than for any other single type of music. (This data was made up, by the way.) So now we can ask a question: is this showing that people who like blues music are more likely to get divorced because they’re so depressing to live with, or that people who have got divorced are so depressed that they’ve gravitated to blues music? Or is there no relationship at all?
The spreadsheet in itself cannot provide answers, but it can suggest possible lines of research, and this is what I demonstrated to the science department. Between them they could think of all sorts of areas in which to use pivot tables in order to get the kids talking and enquiring. Indeed, they became quite excited by the prospect.
Well, it takes all kinds I suppose! And it was definitely good to find out that I’m not the only one who finds spreadsheets so useful.
I like a challenge so I thought I’d try to create a self-marking spreadsheet in Excel. I was inspired to have a go at this by someone called Lee Rymill, who uploaded a self-marking spreadsheet to the CAS resources area. However, I wanted to take it a few steps further, using the Visual Basic for Applications programming language.