Clash Tables in Google Sheets

If you are organising exam diets, it is useful to know which exams you can schedule together without clashes. For this, you need a clash table. The starting data will be a list of all the pupils class enrolments. It'll look something like this, where each row is one pupil's courses:

And your clash table will look like this:

We can see that nobody is taking Spanish and German so we could schedule those exams together.

To construct this clash table, the formula in cell L2 is:

=ifna(query($B$2:$F$5,"select count(B) where (B='"&$K2&"' or C='"&$K2&"' or D='"&$K2&"' or E='"&$K2&"' or F='"&$K2&"') and (B='"&L$1&"' or C='"&L$1&"' or D='"&L$1&"' or E='"&L$1&"' or F='"&L$1&"') label count(B) ''"),0)

Replace the 5 in $F$5 with a bigger number to cover all the rows of your pupil data.

If you paste this into your sheet in your equivalent of L2, then copy right and copy down across your clash table you'll be done.

Let me know if you can see a more efficient way to achieve this.

 

Handling Student Choices with Forms

In this post I'm going to explain how I use Google Forms and Sheets to complete in a couple of hours a task which used to take several days.

At my school, we run "enhancement" classes for all S2 students twice a week. Each of the two sessions has a list of available classes - ranging from games development to crocheting and bushcraft - and we ask students which of the classes they would like to attend, then allocate them to classes (bearing in mind that some classes will inevitably be over-subscribed). If you have to deal with a similar administrative task you may find this post useful. My solution uses Google forms and Google sheets, and relies on some spreadsheet formulas.

In an ideal world, this task would be completed for me by someone else! In reality the wonderful office staff in my school are already working at full capacity; truth be told they are over-worked and under-paid.  And so I need to sort it out. If you are a depute reading this and have managed to avoid all administrative work, please get in touch and tell me your secret!

The previous depute used to give every S2 student a paper form with all the choices on it, and have them write 1, 2, 3 or 4 beside their preferences. He would then spend a weekend (or more) shifting these bits of paper around until he had created classes. He had to complete this sorting task twice - once for each of the two enhancement sessions in the week. The piles of paper would then be taken and typed up for entry into our MIS.

As this task approached, I considered how I might use IT to improve the process. I hoped to save time, and to ensure fairer allocations.

I realised that Google forms would do the job, and created this form for the students to complete:



The original form, created in my school's Google Apps for Education account, used the option to require login and collect usernames, so it didn't actually matter whether the students gave me their names. Once the students had completed this form, I had all their choices in a Google sheet (this sheet is created automatically). At this point it looked something like this (I've cleared the names):

Because I knew I would be repeating this task every year, I decided it would be worth putting a bit of time into streamlining the process, and so I took a copy of the sheet and did the following:

I inserted two columns before the choices, into which the "choice number" allocated to a student would go. For example, if the first student had the number 1 in column F, that would mean they were doing "Ace of Cakes". I then added another two columns which converted the choice numbers into the actual choices in words. This was the tricky bit. The following formula went into cell G2:

=INDIRECT(concatenate("R",row(F2),"C",F2+9),false)

This gave me a spreadsheet like this one:

When a number is changed in column F, the corresponding words in column G change. You can try this for yourself by following the link, as I've made the spreadsheet editable (play nicely!)

This made life much easier for me. All I needed now was a summary of how many students would be in each class, based on the choices they had been allocated. I have exactly that on the second worksheet in the sheet. It looks like this:

This summary automatically updates as I make changes in column F. The formula in cell B1 is

=countif('Form responses'!G:G,A1)

Once I had this set up, it only took me about half an hour to sort all the allocations. I started by putting 1 all down column F. This gave me a summary of how many students had picked each class as a first choice. Some of these totals were fine, but some were over-subscribed. For these, I sorted the first sheet to see all the students who had chosen this, and selected at random however many I had to shift to their second choices. I just changed their 1s to 2s and the job was done!

Having completed the allocations, I had them all in the spreadsheet so could easily print out class lists for entry into our MIS.

Forms and spreadsheets rule!

Forms for Self-Evaluation

Google forms provide a very powerful tool to make me more efficient as a depute. I'll be writing a series of posts describing how forms help me with many aspects of my job.

One of the key roles of a depute is to lead self-evaluation in school. If we don't know where we are, how can we know which direction to head in? Google forms [other online forms such as Surveymonkey are available!] allow me to survey teachers, students and parents quickly and easily. And because the results feed directly into a Google spreadsheet, I can easily analyse the data I gather.

Here's a real example: my school is prioritising the nurturing of a growth-mindset culture amongst students and teachers. In order to establish a baseline, I issued a survey to all S1 and S2 (yr 8 and yr 9) students at the beginning of this school session, which assessed their mindsets, and their beliefs about the mindsets of teachers towards them. Here's a copy:

https://goo.gl/KcDfy4

Creating a form is as simple as going to the google drive homepage ( https://drive.google.com) and going to new>more>Google forms.  Once you have created the form you get a link to share via the "send" button. If you are operating within Google Apps for Education, you have the option to require a login to your system in order to access the form, and can log usernames (not usually a good idea if you want honest submissions!)

 All our S1 and S2 students do either business education or computing every week, so it was quite straightforward (with the agreement of my wonderful faculty head of business and computing!) to have them complete the survey in class.

Once the students had completed the form, all the data was automatically stored in a Google spreadsheet, where the system produces elegant summaries of the data:



This is already very useful. I'll issue the same survey to students in August and we will have a clear idea of the progress we have made.

Having seen the raw data, I was very interested in the correlation between different answers - were the students who viewed mistakes as an important part of learning more likely to enjoy being given feedback, for example? We would imagine so. In order to explore this, I downloaded the data into an Excel spreadsheet, and used the "correl" function to produce a big chart showing all the correlations between the different answers (Google sheets lacks this function). By and large, they matched exactly the correlations that Dweck's work would predict. In the example above, those students were indeed more likely to enjoy being given feedback. This reassured me that our work to nurture growth mindsets was not simply based on the work of an American academic. We now knew that those students in our school who had a growth mindset (believed that they could increase their own intelligence) were more likely to have a raft of other positive attitudes towards learning. This was an exciting confirmation.

I have used Google forms for the following self-evaluation activities, amongst many others:

• pre-surveys before running CLPL sessions, to help me tailor the session to the actual needs and prior learning of staff
• CLPL evaluations
• Having students evaluate me as a teacher
• Having students write reports on themselves

The Digital Depute

I'm a depute head teacher at a Scottish high school. I'm @jonesieboy on Twitter and blog at "Learning Stuff about Stuff".

It has occurred to me recently that I use IT, and particularly Google Apps for Education, almost every day in my job, and that I know how to do lots of useful things using GAfE. This blog exists to share some of those, in the hope that some other teacher or depute might find them useful.