Some notes to assist in the use of the detention workbook.
The spreadsheets in the book are as follows:-
Detentions. This page is where the detentions are logged. I have created spaces for 1999 detentions, which should be sufficient for the average school.
Calculation Page. This page does the number crunching and counts the various items that you will want to count :-
It also includes a copy of the Detentions sheet its-self
All of these can be set up in the way you want and can be altered on the calculation page but more of that later.
Student Detention Graphs. This sets out the students gaining detentions as columns with numbers so you don’t have to count them, what a treat! You will see there are three graphs depicting the top 75 miscreants in the school and their accomplishments.
Staff Detention Graphs. This sets out the staff giving detentions and as such deserves being treated with considerable sensitivity. High numbers can indicate a huge number of things and should not be viewed as a clear indication of incompetence with a view to beating your colleague with it. Enough said.
Reasons For Detention. Sets out exactly what the title indicates. It is an interesting barometer.
Times Detentions Given. This gives out some interesting data and allows you to see exactly when the bad behaviour occurs.
Classes Gaining Detentions. A useful barometer of behaviour, class by class.
Setting up Detentions
First of all you need to enter the names of all the students in the school. I have made space for 199. You can expand this if you need to by copying the formula downwards in both Combined and Detentions Gained columns. Remember to fix the total at the bottom of the list and to copy the slot up at the top so that you can see if all the detentions are being counted. If they are all of the numbers to the right of the column titles will be the same.
One of those little jobs I do in school is to make a class register spreadsheet for myself and it also allows colleagues to make registers for themselves too. I add the student’s names in Forename Surname and Group/Class columns. This is handy because you can sort the data in a number of ways, you might also add Male/Female if you want to do further sorts.
The above little sideline gives me a list of all the students in school, which I can copy and paste into the Forename Surname and Group/Class columns. That having been done, Forename and Surname are combined into one slot by the formula contained in the Combined column of slots. These combined names are the data used by the formula in the Detentions Gained column to look through the spreadsheet and count all the occurrences of that particular name.
Secondly add the staff names as described above Richard Murray =RMu. There are 38 slots for staff. This can be expanded as described in First in Setting up Detention.
Then decide upon the reasons for giving detentions and then add a code for them. For some reason Excel seems to like more than one letter to describe these or it will not always count them. I don’t know why but sometimes it won’t, but it does count two letters consistently.
Next add the number of periods in your day 1 2 3 etc add T for Tutor L for Lunch and B for Break. Already typed in are 1,2,3,4,5,6,7,T,B & L. Their descriptions are the left and you may need to change these.
Finally add your Group/Class codes. There is space for 15 already installed although you may need to expand this. See First in Setting up Detention.
Using Detention
Have a look at the Detentions page at the front of the workbook. There I have detailed the heinous crimes of the few notably Farker Arbuthnot. I’m sure you have a Farker in your school ;-).
If you work your way through the book you can see how the Calculation Page does it number crunching. From this page all of the graphs are plotted through links pasted to the relevant pages.
Try adding a few more detentions in a name such as Petunia Aston of 7A in CBa’s name as that person never gives any detentions and then have a look at the way the graphs have changed.
Our boss found this way of displaying data very useful and quick as I work on the same part of the split site as him. The secretary, who logs this and other data using Microsoft Access works on a different site and this slows down the delivery of data.
Another useful facility Excel has built in is Filter. This is the array of little grey boxes with arrows pointing down the columns of slots. If you click on any of these a dialogue box opens displaying the contents of the column. If you click on the filter under Student column you get a list of all the students names (who have gained detentions) plus All, Top 10, Custom and under all of the names Blanks and Non-Blanks. Click on Farker Arbuthnot and all of the other names disappear leaving just him. This is great for printing. You can sort by date, group staff, reason, lesson etc Go to Help if you don’t know how too. This list can be printed and only young Arbuthnots detentions will be printed. This is a real treat if Mr Arbuthnot pops in for a chat regarding the staff picking on him or as evidence if you have summoned him into school. To reset the list click on the arrow and either select all or another student. Using custom within filter, you can display the detentions between two given dates or using a combination of filters which student, member of staff or particular offence was involved. The best idea is to play a bit.
Using filter has no effect on the graphing but it is wise only to use the Detentions sheet when doing this.
We have found the notes at the end very useful as they describe exactly what has happened so you are not searching for reports or similar to assist with reviews on individuals.
The Completed column is used to indicate which detentions have been carried out by the students. I tend to keep resetting it to blanks so I just have to look at the pending detentions. You can use any code letter you like here, I use an X as can be seen. Really that one is down to your personal choice.
I would suggest that you make a backup of the Detentions Workbook. Make a copy and delete the detentions data and add your students names, staff names etc.
You will need to back this up from time to time so you don’t lose data. You might also consider printing out the Detentions page now and again too for the same reason.
If you make a copy of your working version of the workbook and want to draw graphs for a particular group of students or just a few odd individuals you can select what you want to use using filter on the Detentions sheet of your working version and paste them into the copy. All the graphs will work as before but it will be a more select analysis.
I find this useful. You may find it useful too. Please use it if you wish and good luck with Mr Arbuthnot when he pops in.
Richard Murray
Abbey Hill School Technology College
Work Email rmu@abbeyhill.stockton.sch.uk