A self-marking spreadsheet, by Terry Freedman

The following article was first published on 21st October 2013. I’ve made very few changes to it.

I like a challenge so I thought I’d try to create a self-marking spreadsheet in Excel. (Look, some men like fast cars, some like sport, and some like womanising. Me? I like spreadsheets. OK?)

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…

Lee’s spreadsheet had the answers “hard-wired” into it, ie the answers were in the formulae, like this:

=IF(C3=25,"Correct","Incorrect")

I wanted to create a spreadsheet that was more generic.

Also, I wanted the spreadsheet to:

• Count the number of correct and incorrect answers

• Give the student feedback

• Tell the student where to to go for help or what to do next.

What I came up with seems to work, and can easily be customised for any test or quiz where a particular answer is either right or wrong. If you decide to use it, you will need to:

• copy the formulae down as far as you need to

• obviously save the file under a different name.

I really intended this as a proof of concept.

You could also use it as a means of demonstrating how Visual Basic for Applications (VBA) can be used in the context of Excel and other Microsoft applications (although there is some variation between applications). Even if you don’t intend to teach VBA as one of the required programming languages, this spreadsheet is a good demonstration of how programming can make life easier and more interesting for the user. It does this both in the background, and overtly:

If the student clicks the button labelled “See the rubric”, they see this message. If they click OK, the hidden answer worksheet becomes visible and they are taken to it. If they click Cancel, they see the following message:

A self-marking spreadsheet, by Terry Freedman

 

• Why not inject a bit of humour into your self-marking spreadsheet? See the screenshots above for examples.

• Hopefully, this will also demonstrate that spreadsheets don’t have to be as dry as dust: what’s wrong with a bit of humour in lessons?

• Other messages pop up, depending on whether the user clicks on “OK” or “Cancel”.

If you decide to give this a go, you’ll need to make sure your security settings in Excel will allow you to run a spreadsheet with macros. The PDF explains how it works. Feedback would be much appreciated. (I can think of one or two things I’d change myself, but I could go on tweaking forever!).

To access the files, subscribe to Digital Education, the free newsletter. Then go to the free subscriber resources area indicated in the welcoming email. Since writing this article I’ve added another spreadsheet, the Excel Grade Predictor. Enjoy!

Related articles

This article was published on this day 3 years ago. I’ve had to clean up a few links and delete some out-of-date references, but otherwise the article still makes some useful points about assessing Computing and related subjects using rubrics.

Some years ago I was approached by Julie Lindsay and Vicki Davis with an invitation to be a "meta-judge" on the Horizon 2008 Project. It was a great honour to be asked, and I hope my judgements are received in a positive way.

But, as usually the case with this sort of thing, it did raise doubts in my mind about the value of rubrics for this type of activity. They are useful, but they are also limited, and not nearly as objective as one might think.

The Horizon Project involved students from several countries collaborating with each other to do research into how modern technology is affecting various aspects of modern life (government, education, health and others). The end product, besides the wiki itself, was a video submitted by each student. These have been judged by a number of educationalists, who decided on the winner in each of the 13 categories. My role as "meta-judge" was to decide which of these 13 finalists was the ultimate winner.

I have to say that this was not an easy task despite having the rubric to guide me. It isn't easy on a human level, if I can put it that way. The trouble with identifying one winner is that by doing so you automatically identify 12 "losers"! I would hope that those 12 don't see it that way. The quality of all the videos was extremely high, and there are even one or two that didn't come out on top that I will have no hesitation in using in my own work (with full credit and citation given, of course). To end up as one of just 13 finalists is good going, and all of the students should feel proud of themselves.

Indeed, even those students not in the final line-up did a fantastic job. The wiki (now sadly no longer extant) boasted a cornucopia of ideas and resources, almost all of which were put together by the students.

The rubric I used was called Rubric 1, Multimedia Artifact. As rubrics go, it wasn’t bad at all. It was shorter than many, which is good, because the longer, ie more detailed, they are, the more easy they are to apply, but the less meaningful they become. The reason is that once you start breaking things down into their component parts, you end up with a tick list of competencies which, taken together, may not mean very much at all. That is because the whole is nearly always greater than the sum of its parts, so even if someone has all of the individual skills required or, as in this case, has carried out all of the tasks required, the end result may still not be very good. So you end up having to use your own judgement about how to grade something, which is exactly what a rubric is meant to avoid in the first place. Let me give you a concrete example.

One of the sentences in the rubric read:

"Content is constructed from a superficial synthesis of information on the wiki."

That seems straightforward enough, until you come across a case where the content on the wiki page is itself superficial -- in which case the right thing for the student to have done would have been to ignore the wiki page all together and put in some fresh insights. But if they had done that, they wouldn't get credit for using the information on the wiki page. In other words, it's a no-win situation which actually penalises the student who exercises her own judgement.

I think the main problems with rubrics in general can be summarised as follows:

1. Do the individual criteria reflect what it is we are trying to measure? This is (broadly speaking) the problem of validity.

2. Are the criteria "locked down" sufficiently to ensure that the rubric yields consistent results between different students and between different assessors (judges)? This is known as the problem of reliability.

3. Are the criteria too "locked down", which could lead to an incorrect overall assessment being made (the validity problem) or assessors introducing their own interpretations to aid the process of coming to a "correct" conclusion (the reliability problem)?

4. Does the rubric emphasise process at the expense of product? It is often said that in educational ICT, it's the process that's important. Well actually, that is not entirely true, and we do young people a grave disservice if we fail to tell them so. If you don't agree with me, that's fine, but I invite you to consider two scenarios, and reflect which one is the most likely to happen in real life:

Imagine: Your Headteacher or Principal asks you to write a report on whether there is a gender bias in the examination results for your subject, in time for a review meeting next Wednesday. You can't find the information you need, so you write a report on the benefits of blogging instead. You desktop publish it so it looks great, and even burn it onto a CD for good measure. To add the icing on the cake, you even make a 5 minute video introducing the topic in order to get the meeting off to a flying start.

Scenario 1:

The boss says:

"Wow, that is fantastic. It's not what I asked for at all, but let's face it, it's the process that's important. Let me raise your salary."

Scenario 2:

The boss says:

"What is this? I asked you to produce a report on gender issues. If you can't follow a simple instruction like that, do you really think you're cut out for this job?"

OK, I know that both responses are slightly far-fetched, but hopefully I've made my point.

Which also leads me on to another thing. I think some of my judgements may have come across as a bit uncompromising. But I really do not see the point of saying something like "Great video", or even "Poor video", without adding enough information for the student to get a good idea of why it was good or poor, and how to improve their work and take it to the next level in the rubric.

Getting back to the issue of interpretation, I am afraid that, in the interests of better accuracy and of giving the students useful feedback, I introduced some of my own criteria. Well, I was the sole meta-judge, a title so grand that I felt it gave me carte blanche to interpret the rubric as I saw fit. Lord Acton was right: absolute power really does corrupt absolutely .

The extra criteria I applied were as follows:

1. Did the medium reflect the message?

To explain what I mean by this, let me give you an example of where it didn't. In one of the videos, the viewer was shown some text which said that businesses can now make predictions. This was then followed by a photograph of chips used in casinos. So, unless the video was intended to convey the idea that predictions can now be made which are subject to pure chance, which I somehow doubt, that was a completely inappropriate message.

2. Could I learn what I needed to know about the topic without having to read the wiki? If not, then I would be at a loss to explain the point of having the video, unless question #3 applied. This includes the question: is the information given actually meaningful? Look at that point about businesses can now make predictions. Businesses have always made predictions, so that statement tells me nothing. What I want to know is, how does communications technology aid forecasting, and does it make the process more accurate?

3. Did the video inspire me to want to find out more, or to do something, even though there wasn't much substance to it? If so, and if that was at least partly the aim, maybe that would be perfectly OK. I'd take some convincing though.

4. Did the video only synthesise the information on the wiki, or did it do more? The word "synthesise" implies adding value in some way: it's more than merely "summarise". But if if the information was of a poor quality, did the student deal with the matter effectively or merely accept the situation?

5. In every case I watched the video first, and then read the wiki, because I wanted to come to it with as few preconceived ideas as possible, to see if the video was able to stand on its own. I then read the wiki and then re-watched the video (sometimes more than once), looking for specific things.

If you have any views on using rubrics, I'd love to hear them -- especially if you completely disagree with anything I've said in this post!

* The Computers in Classrooms newsletter was launched in the year 2000, and is now called Digital Education. It has loads of subscribers, who say things like:

“Many thanks Terry,  for your contributions. I am pointing my students to your site.”

“WOW! I don't know what possessed me to link to your newsletter, Terry, but you have captured, in the sample issue sent to me, what seems to be the "top ten" list of significant educational issues and boiled them down into - "manageable undertakings?!" Don't let the teaching colleges find out about this, or they will run you out of town.”

“I greatly respect the ethics you bring to your work!!!!”

It’s free to subscribe, and there’s more information here: Newsletters.

This is a modified version of an article that was originally published on 9th June 2008.

 

Permalink

Print Friendly. Screenshot by Terry Freedman

Squarespace, which is the service I use for my blogs, is great, and has some lovely design features. However, by their own admission, the blog posts created in Squarespace could not be described as “printer friendly”. Look at the screenshot below, for example, which shows my attempt to print out an article:

Printing an article from a Squarespace blog, by Terry Freedman

I attempted the printing by moving the mouse within the article, and then right-clicking to reveal the Print option in the menu that pops up. Squarespace itself does not provide a print button in the same way as, say, a message in Gmail. As you can see, it’s not just the article that is going to be printed, but the web page, complete with heading and sidebar. The result is that more pages than required are generated, and strangely enough not every sentence appears in the hard copy: bits are missed out from the bottom of pages.

This unfortunate situation is not so much a glitch as, from what I understand, a deliberate decision to weigh up the trade off between a nice visual layout and a decent print version, and come down firmly in favour of the former.

Fortunately, there is an answer. This comes in the form of an application called Print Friendly. Go to their website and install the browser extension (the main browsers are catered for). Then, when there’s an article you wish to print, go to the article and then click on Print Friendly in the browser toolbar. Here’s what that same article looks like in Print Friendly:

Printing an article using Print Friendly, by Terry Freedman

You will notice that this time it is only the article that is going to be printed rather than the web page on which the article resides. It’s not perfect — look at the way the text butts up against the illustration, which it doesn’t on screen. But it’s a big improvement, especially insofar as there are no missing sentences.

Although I’ve used my own website as an example in this article, I should note the fact that Print Friendly really comes into its own on those dreadful websites that are full of adverts. You know, the ones where when you print the article you end up with all the adverts and about three sentences of the article itself on each page, of which there are many. Print Friendly deals with this situation excellently — I seem to remember that there was one website which even had Print Friendly foxed.

It’s definitely worth installing.

Print Friendly has an option to save the article as a pdf if you don’t wish to send it to the printer.

All this, and it’s completely free.

Updated!

Since publishing this article I have amended the details of next year’s Summit slightly. If you have already read this, just skip to the end for the updated details.

Opening remarks

Idea!, by Terry Freedman

Getting into schools is hard. When I was a teacher, and then head of a local authority ed tech provision, my approach to dealing with marketing letters was to throw them away, and I never took phone calls from companies because I didn’t have the time — unless by some stroke of fortune a company with exactly the kind of product I was looking for happened to get in touch just at that time. Now that I know what it’s like on the other side of the fence, I feel quite guilty —although, of course, I shouldn’t. Let’s face it, with five “free” periods a week to lead and manage a Computing department comprising 5 teachers, prepare lessons, mark work, observe others’ lessons and so on and so on, how could I have justified responding to cold calls?

The Marketing to schools summit was the Bee Digital Education Marketing Agency’s attempt to help companies get their products and services into schools. That’s what I’m reviewing here.

Before embarking on this review I think I should tell you three things. First, I’m quite cynical. Back in the 1980s there was a spate of courses and seminars on the subject of how to attract money into your life. I always felt that perhaps the best way of doing so would be to run a course or a seminar on the subject of…. So when I was told about the Marketing to Schools Summit, I thought, “Oh yeah?”

However, two factors inspired me to attend. The first is that I’ve known Bryan Plumb, the CEO, for many years, and as far as I’m concerned he is as trustworthy as the day is long. (Yeah, I know the days are getting shorter in the northern hemisphere now, but please belt up!) The second is that although I’ve long given up believing in silver bullets, a part of me thinks that, well you never know. As I said to one of the speakers before the conference started, “We’re all here epitomising Dr Johnson’s observation of the triumph of hope over experience.”.

The second thing I should tell you is although I think of myself as a visionary, and although I am no longer a full-time teacher, I still think in a very down-to-earth way when it comes to conferences. To put it boldly, in every presentation I am thinking, “This is great, but how will it help me with class 4B on Friday afternoon?”

Thirdly, I was offered a free place at the conference. However, that has not affected my write-up of the event.

Let’s look at the three manifestations of my frame of mind before attending, namely:

• Cynicism: was this just a way of generating money for a marketing agency?

• Silver bullets.

• Practicality.

Cynicism

My cynicism was misplaced. Both the presenters and participants were there in the spirit of helping the community. All of the presentations I saw shared useful information, much of which would usually be regarded as a company secret. I thought it was very generous of them.

Silver bullets

As one of the participants said to me, we all came hoping for a silver bullet even though we know there is no such thing; but it’s good to be reminded of things we’d forgotten.

A good example of this for me was something that Jodie Lopez said: the quality of your lead magnet (free giveaway) should be as good as the actual product. Funnily enough, I’ve been working on a new lead magnet for my newsletter, Digital Education, and a few weeks ago resolved to commission a professional cover for it. But in the last week or so I’d been thinking that that would just delay issuing it, so why not just make it available with a plain cover and be done with it? But Jodie’s comment set me back on track.

Practicality

Just three examples: Laura McInerney shared some very interesting and useful data about teachers’ social media habits, garnered from Teacher Tapp. Anna Pedroza, who runs the eponymous PR agency Pedroza Communications, gave us the lowdown on how to contact editors. I found her advice very valuable, and indeed used it with some success today — and I’ve been pitching ideas to editors for years. Bryan Plumb told us the 9 word email he uses to re-engage disengaged newsletter subscribers. And Jade Parkinson-Hill inspired us by relating her live-streaming journey to what was to become STEAM School.

Wish list

There are a few things I should have liked to see. (Note: I had to leave at lunchtime, and haven’t looked at all the presentations yet, so perhaps these were covered.)

• I’d like to have heard more about word-of-mouth and other organic forms of marketing. Is organic marketing dead? I certainly have the impression that these days in order to sell books and get loads of people signed up to a mailing list you have to advertise, but is that perception correct?

• Possibly not the time and place, but Bryan very rightly said that if one of your customers writes a better manual than your own, why not pay them for it or commission them to write one? I’d like to suggest, as he didn’t, that even if teachers don’t specifically ask for this, you should offer author-friendly terms, ones that are in keeping with the Society of Authors’ guidelines. If you’re not sure what they are, read the advice to educational authors who have been approached by publishers.

• Although I came away with several actionable ideas, I don’t have a huge company, and it will take me a while to implement them. As an almost one-man-band I have to balance immediate income against marketing for the future. What’s the advice for getting into schools for micro-businesses such as mine? Facebook ads?

• What about advice for those of us who are, shall we say, introvert marketers? I’ve been successfully independent since 2004, and I’ve largely avoided networking events, crowing about my achievements on social media, or even listing my clients on my website. Yet to borrow from Elton John, I’m still standing. So what can people like me do to gain even more clients without going against our principles or having to become something we’re not? (By the way, introverted does not equal shy: I’m very happy to speak in front of hundreds of people, I just don’t like speaking about myself.)

Conclusion

Would I go again? Yes. Would I recommend it to others? Definitely. And fortunately, this was not a one-off event. The next one is taking place in London in October 2020. You can see the agenda for the one just gone on the conference website. As I’m not sure if that will still be the website for next year’s summit, I suggest you keep tabs on the main Bee Digital website for announcements.

And in the meantime, put it in your diary.

Exclamation mark, by Terry Freedman

Thank goodness for the scheduling facility in Squarespace. It has enabled me to write this blog post a long time ago.