Excel 4 Teachers pt.1 – from UMS to grades

How amazing would it be if you could be that colleague with the colour-coded Excel spreadsheet which calculates totals, tells you the UMS points AND transfers this into grades all in the time it takes to slap three kids into detention for sticking their bogies to the door handle? Well now you can, and here’s how…

Step 1: set up your columns

I’ve set my sheet up for Unit 4 of the Edexcel GCSE in French, but you can obviously do this for any assessment, scheme and exam board. First of all, simply head your columns appropriately for your marks:

My spreadsheet is set up with columns for students’ names and their marks.

The columns and rows are named by Excel with numbers and letters: column A contains the names, columns B, C and D the marks and columns E, F and G the total, UMS and grade respectively. Thus, cell G3 will contain Andrew’s grade for the task.

Step 2: totalling up the marks

Stop being that teacher sat at their desk with a calculator your HoD ordered from the supply catalogue – get Excel to do it for you. In the total column, type the following formula:

=SUM(B3:D3)

Then press enter. This means that all of the cells between (and including) B3 and D3 will be totalled together in the column containing the formula. You can then copy and paste this into all of the cells beneath (CTRL+C to copy, CTRL+V to paste). Now, when you type numbers into the first three columns of your sheet, Excel will total them up for you. Bear in mind that the total will (logically) show as zero until you input some data. Try putting a ‘1’ into each column and checking that the total comes out as three.

Watch out! – B3:D3 is the range of columns I’m totalling in this spreadsheet, but it may not be in yours – check which columns you actually want to total and adapt the formula accordingly.

Step 3: calculate the UMS

Now, instead of looking up each individual student’s UMS score, you can programme Excel to do it for you.

To do this, you first need to find the raw score to UMS conversion table for your exam board, (the Edexcel 2011 French conversion table can be found here). I have then just copied and pasted the table for GCSE writing into the empty columns on my spreadsheet:

My spreadsheet now contains the exam board’s raw score to UMS conversion data.

Beware! The raw scores and UMS must be entered in ascending order for this to work (ie: starting at 1 and working upwards numerically as you go down the sheet).

What you now need the spreadsheet to do is look up the total number of points your student was awarded (cell E3) and then look up the corresponding number of UMS points and put this figure into column F. Unsurprisingly the formula for this is ‘LOOKUP’:

=LOOKUP(E3, H3:H62, I3:I62)

This formula means the the spreadsheet will look up the value in cell E3, find it somewhere between cells H3 and H62 and then display the value in the next column (cells I3 to I62). Thus, if Andrew is awarded marks of 10, 7 and 2, with a total of 19, the spreadsheet looks this value up and calculates the relevant UMS points as 36.

Unfortunately you can’t just copy and paste this formula into the cells beneath, beacuase it will change the search ‘vectors’ (ie: the range of cells where your raw marks and UMS are stored). To stop it from doing this, simply highlight the two search vectors as shown and press F4:

 

Watch out! If you’re using a Mac (like me!) you’ll need to type the $ signs in manually, but on a PC pressing F4 will do that for you. Your formula is now ready to copy and paste into all the cells beneath.

Step 4: calculate the grades

Our final step is to have the spreadsheet convert the numerical UMS into letter grades. To do this, we first need to enter the grades next to their corresponding UMS according to the exam board’s grade boundaries, which can usually be found in the course specification:

I have entered the grades next to the UMS according to the grade boundaries in the specification.

Now all we need to do is repeat the same process for step 2, but modifying the formula so that it searches columns I and J instead of H and I:

=LOOKUP(F3*2, $I$3:$I$62, $J$3:$J$62)

Note that I have also inserted *2 next to F3; this is because for the Edexcel French GCSE, students submit two pieces of controlled writing, but we only have the marks for one. I therefore need to double the UMS score as the exam board’s grade boundaries are based on the premise that the UMS are totalled for two pieces of work.

Once copied and pasted, this gives us a filled-out spreadsheet with each students’ grade:

Hopefully none of that was too complicated. I’m certainly no genius when it comes to computers, so if I can do it anyone can! If you get stuck, comment below and I’ll see if I can help!

ICT in MFL – acronym heaven!

My school recently advertised for a member of each department to be ICT Champion for their subject… naturally, I couldn’t resist! But before writing the one-page proposal for how technology could be applied within the MFL department and why I was THE person to do it, I had to have a think about what I actually knew.

What about Q23?

We all remember well Qs 16 and 17 of the Professional Standards for Teachers – you know, the ones about passing an ICT test that had nothing to do with programmes people actually use, and the one that you validated by using a ‘custom animation’ in your latest PowerPoint that your mentor thought had come straight out of Star Trek. But what about the Q that time forgot: Q23?

In observing my trainees, I rarely pay much attention to what they’re doing with PowerPoint/SmartNotebook/Promethean (delete as applicable) – for me, the point in using ICT in the classroom is to enable a higher level of engagement, interactivity and independence from the pupils: Q23 – Design opportunities for learners to develop their literacy, numeracy and ICT skills. Ie: the kids have to use it too.

Student-based programmes

With this in mind, I sat down to think about all of the times my students had used ICT in my lessons to enhance their learning. This was not easy – whilst the will had always been there, poor WiFi connection and batteries that refused to hold their charge for a full hour often scuppered my plans. Still, there were some times when I felt genuinely pleased with the result, and none of those involved students copy-pasting from Wikipedia to PowerPoint and then reading their work to the class, stumbling over the Americanisms they had lifted but didn’t understand. Here are my top favourites:

1. French History Project (y7)

The VLE at my last school was (although not perfect) truly interactive and pretty user friendly: it was called RealSmart. Using the ‘mind-map’ application (unsurprisingly named ‘RealMap’), I created a three-hour long project for year 7 students on French historical periods.

Lesson 1 saw students in groups named after French regions logging on to the Histoire française RealMap from their netbooks and clicking on the coat of arms for their region. They were then presented with a series of reading and listening resources based on different periods of history (one per group) which they used to complete the exercises on their worksheets. All of the language in the resources was based on structures we had seen throughout the year so this was a great way to show their use in a contextual situation. During lesson 2 students broke away from their former groups to form new teams, with one student for each period of history per team. They then shared their information, teaching the other students in their group, before planning the final activity to be completed in lesson 3 – creating a timeline of French history.

This project was not only a great way of introducing cultural information into the MFL classroom whilst still practising essential language; it also enabled students to develop their ICT skills in a controlled way, without just resorting to Google and Wikipedia.

2.  VocabExpress

Whilst the next offering wasn’t really something that I created or even used in lessons, I couldn’t write a post about ICT in MFL without mentioning it.

VocabExpress is a web-based programme for – you guessed it – learning vocab. We all know that there is just not enough time to drill vocab and teach structures in lessons, so VocabExpress is the ideal accompaniment to any MFL course. Students log on with their own ID and complete different learning activities for a given list of words, chosen by the teacher (all of the vocab that has been uploaded is taken from the main coursebooks and so can be easily linked to any GCSE or A Level topic). The programme is intuitive, so when students make mistakes, it remembers and gives them those words more often. The teacher can monitor everything they do, from when they log in to what exercises they have completed and how well they did. Scores are then converted into points and a leader board is displayed of the whole class. There are also rankings for each class within the school, each school in the local area and there is even an annual national competition where schools from across the country compete for points.

The students LOVED it; the competitive element coupled with the fact that they could log on and learn whenever they wanted made VocabExpress a real success.

3. Blogging

The third and final activity in my list of favourite uses of ICT in MFL is a shameless piece of theft, but I am sure that its creator will not mind me stealing it!

My PGCE/Master 1 student on her second placement with us has been teaching her y7 French class about hobbies and free-time activities. Towards the end of the term, as a culmination of the work they had been doing, she asked them to bring in their netbooks and write a blog on their free-time activities. ‘Not very creative’ I hear you shout? Well wait for it…

Once the blogs had been designed and written, students were asked to include a space for ‘reader comments’ just like a real blog. They then left their netbooks in their places and moved around the room, reading their friend’s texts and leaving comments in French (which had been introduced previously). This was such a simple and yet amazingly creative idea, and an excellent way to get students giving their opinions in a real context. If this had been coupled with the RealSmart blogging facilities (RWeb or RCast) this would have been a truly interactive experience.

ICT means money

Now I know what the more cynical among you will be saying to yourselves: where did all of these bloody netbooks come from?! It’s true that 95% of the students at Homewood had signed up to a part-rent part-buy laptop scheme which meant that they all had their own netbook. I can’t deny that, if this isn’t the case in your school, buying a laptop trolley for a department can be very costly. Most schools, however, do have ICT suites which, with some planning, could be booked for a series of lessons to enable some of these activities to take place. The main point is that you really should make the effort – it will be well worth it!