Creating a RAG system in Excel tutorial

It may sound a bit sad, but I love Excel – once you have mastered a few simple techniques, you can put these techniques together in different orders to create some very powerful effects, and for me one of the most powerful things that I can do in Excel, is analyse some data in a way that will visually highlight an issue to me, so that I can act upon that issue quickly. One such technique that I (and many others) use is using a RAG rating system. RAG stands for Red, Amber, Green – (based on traffic lights), where things that are on schedule and up to date are Green, things that are a possible concern are Amber, and things that are a significant concern are Red.

Image showing a simple RAG system created in Excel

This following set of videos, are designed as a tutorial to teach you the skills required to create an effective RAG system within your own Excel files.

If you like this tutorial, then please subscribe to my YouTube Channel at:

https://www.youtube.com/channel/UCWuDqvf7nO6-00JMMxm1lIw?view_as=subscriber


Introduction

The first video is an introduction, showing the end product of what will be created.


Using the Now() function

The Now() function is a very simple way to bring today’s date and time into a cell within the spreadsheet, which can then be used to compare against other dates within the spreadsheet, e.g. to see which are in the past or future.


Using a basic IF statement

The IF statement in Excel is one of the simplest and most powerful ways to carry out analysis of data in Excel.


Using a Vlookup function

The Vlookup function, seems a little confusing at first, but once used a few times is relatively straight forwards – and allows you to lookup a value in the left hand column in a table, and then return a value from a specified column in the same row of that table.


Using the Max and Min functions

The Max and Min function are very simple to use, and will tell you what the largest or smallest value is in a list.


Using Conditional formatting to create horizontal bars

Conditional formatting is where the appearance of a cell changes based on values (either the value of that cell, or different cells). This video looks at creating horizontal bars that move further right as the value increases.


Using Conditional formatting to create icons

Another option when applying conditional formatting is to add small icons to cells, for example up and down arrows, traffic lights, warning flags etc.


Putting this altogether to create the RAG system

The final video shows how the skills covered above can be put together to create the desired RAG effect.


I hope that this tutorial has proved to be useful.

If organisations want training providing in things like using Excel more effectively, then please get in touch via http://www.a6training.co.uk/contact.php

Advertisements

Template to quickly create a 2 Circle drag and drop activity in Moodle

On Monday I released a template that I have created allowing people to easily create a 3 circle Venn diagram activity in Moodle. Today I have created and released a similar template for a 2 circle Venn diagram activity.

The template is PowerPoint based, and allows the teacher, to quickly and easily create the Venn diagram with the correct dimensions, and then the required coordinates that Moodle uses to identify the different zones are provided for you, so it is possible to create such an activity in a matter of minutes rather than hours.

The following image shows how the activity looks in Moodle, in this case I have used a chemistry example – the beauty of this type of activity, is that it can be used in any subject area (not just maths).

Image showing the example activity

The template file for this, can be downloaded directly via:

http://www.a6training.co.uk/resources/2CircleVennDiagramActivityForMoodle.pptx

And a video explaining how to use this is:

I will be adding more similar templates to this collection in the coming days and weeks, and they will be available at:

http://www.a6training.co.uk/resources_Moodle.php

If you want to keep up to date with similar videos, then subscribe to my YouTube channel via:

https://www.youtube.com/channel/UCWuDqvf7nO6-00JMMxm1lIw?view_as=subscriber

 

Planning for ‘snow days’

About 5 years ago I was supporting an institution – who due to their geographic location, would have numerous days each year affected by snow. As an organisation they were determined to stay open as much as possible (otherwise they would go entire weeks without provision), and so they planned for ‘snow days’. These were a mixture of subject specific student paced activities that could be taught at any point during the calendar year, and then generic sessions teaching things like study skills, careers sessions etc. The idea was that if on a snowy day, half the teachers and a quarter of the students couldn’t get in, they could simple use these planned activities to provide education to the learners with the resources (teachers that had made it in). They even had a member of staff (who lived within walking distance) whose duty on such a day was to co-ordinate which students would get which activities to ensure they didn’t have wasted repetition. This model worked really well, the staff and students were used to it, and accepted it, and because the sessions had been planned in advance (and once planned are valid for future years) – they were quality sessions, not something thrown together at the last minute just to fill time.

Snow covered roadAbout 3 years ago, I was contacted by a college to provide training for some of their teachers. The idea being to improve their skills at creating ‘online’ learning activities that are self paced and can be run with minimal tutor interaction for that particular activity. The way that I ran the session, was to create online versions of the ‘snow days’. I had one attendee from each subject area, so the first challenge was to identify a part of the curriculum that can be taught in isolation to other parts, and most importantly at any time of the year between late November and the end of March. Once that topic was identified, it is then building the learning activities for that topic, usually in the form of 5 stages as following:

  1. Providing them with some content – e.g. links to websites, videos, or files.
  2. Asking them a set of challenging questions around that content, to help focus the learners on the key points, and to think critically about the content. Depending on the subject, these may be closed questions that can be tested with a quiz where the computer gives instant feedback, or open questions where students either discuss with their peers via some form of forum activity, or more individual questions, where the student either brings answers to a future classroom activity.
  3. Designing an activity in which the learners will use the information from the above, to do something creative. This could be designing a poster to explain the concept, writing a press release from the eyes of a certain person, creating a mind map of the key information, etc.
  4. Designing a ‘stretch and challenge’ activity – e.g. something optional that the more able students can do if they want, but are not obliged to. In simple terms, this is usually a challenging, discussion provoking question posted into a forum.
  5. Assimilating the above into an area on the VLE in a way that is self explanatory, can be hidden until required (and then un-hidden easily).

The idea here, is if the organisation has a ‘snow day’ or similar (flooding, swine flu etc.), they have something already planned, which is easy to administer, can be completed by both the students that have manager to get in, and for some of those that haven’t, and if the weather is such that it hasn’t been required during the year, the teacher can just run this anyway at a convenient time for them.

I have run similar training sessions with other providers since (including schools, and a University), and they have proved to be very successful – not just training staff in a different way of teaching, but at the end of the day they have a tangible product (a planned ‘snow day’), and for one organisation in particular, this was picked up favourably by a future Ofsted inspection.

If any organisations are interested in me running such training days for them, then please get in touch via http://www.a6training.co.uk/contact.php – I have already had 2 communications this morning, from teachers at organisations that will today be using the activities planned in these training sessions.


Image source: https://pixabay.com/en/winter-snowy-street-frozen-snow-1209348/

Respect for teachers

The following cartoon I think sums up the state of education in the UK at the moment.

Teacher Christmas wish

There are 2 issues identified here, one is the state of funding, which is widely recognised across the state funded sector as having gone down in real terms in recent years (despite the Government pretending that it has gone up!), but the issue I want to mention here, is the general lack of respect for teachers.

Education has always struggled with its position in society in terms of is it professional or non-professional? For someone to teach in a school, they should have passed through the respected rigour of a degree and a post graduate education course or equivalent, and as such they are and should be seen as professional. In FE it is less clear, but all people teaching should be adequately trained, and even if not should be professional in their behaviour, conduct and attitude, so again this should be straight forward.

However (and this is a big however), education itself is very good at behaving professionally when it suits them, but then behaving non-professionally when it suits them. For example if a member of staff is performing badly, and isn’t capable of doing their job properly (and young peoples’ futures are being damaged by this poor performance), then that teacher ought to be supported, given additional training, but if they are still not performing, then the professional thing to do would be to remove them, but in many cases the managers take the unprofessional approach of keeping them on, and ignoring the problem, or worse still promoting them out of the way.

Then to add to this, there are numerous Government driven agendas to standardise and in theory improve education, when really when unpicked they boil down to a lack of trust and respect for the teachers and head teachers, to do what is right, and the damage that this has, is it drives many good teachers out of the classroom, it increases the workloads of the remaining teachers, and ultimately weakens the education our younger generations need and deserve.

I cannot offer any magic solutions to this problem, some of it is rooted historically, and some is too heavily politicised to change – but as an individual, and as a parent, I have a lot of respect for teachers, as I know that teachers will be working flat out until the end of term, and many will be working over the Christmas break with marking, preparation, and many other things – when really a true profession would allow them the time off that they need and deserve to do their jobs effectively.

Free multi-choice patience activity template

When I worked as a teacher, as well as using technology during the teaching and learning process, I also often used it to create activities that didn’t use technology during the actual session. One such activity that I created is something I have called ‘multi-choice patience’. This is a series of ‘cards’ that are printed out and given to the students. Each card is numbered and contains a multiple choice question, with 4 possible answers (1 correct and 3 wrong). Answering each question directs the learner to the next card. To complete the activity the learners have to create ‘loops’ e.g. if using the 36 card set, the answer to the 6th card, should point back to the 1st card in that loop. If it doesn’t then one of the 6 questions has been incorrectly answered, but the learner doesn’t know which one, so they have to go back and try different options, until they correctly complete the loop. Once a loop is created, they pick another card from the pack and start again trying to create a ‘loop’.

Multi choice patience

Screenshot of the multi-choice patience activity

I generally used this activity in the last week of term, when the learners were not up for anything too heavy – I would have the learners in groups of about 4, and they would race against the other groups to see which group could complete the challenge the quickest.

To create the cards, I created a template in excel, where I entered the questions and answers, and the computer randomised the answer order, and worked out the ‘loops’, randomly changing the options each time, and it is this template that I have shared so others can create similar activities.

If a teacher wants to be even cleverer, you get the learners to design the questions in one week (and you could set up something like a Google form that the learners populate) – you then check the questions, copy them into the grid, print out and cut up.

I have recently changed the template, so rather than being limited to having to have exactly 36 questions, it will now work with either 36, 30, 25 or 20 questions.

The template itself can be directly downloaded from:

http://www.a6training.co.uk/resources/MultipleChoicePatience2017.xls

A complete example can be downloaded from:

http://www.a6training.co.uk/resources/MultipleChoicePatienceEXAMPLE.xls

And other similar activity templates can be viewed at:

http://www.a6training.co.uk/resources_class_management.php

A video showing how to use the multi-choice patience template is:

The 4 stage model for use of a VLE

A major part of my work at the moment, is working with and around VLEs, either by creating content and activities, or providing training to teachers or learning technologists in the effective use of the VLE. As part of my work in this area, I have identified that there are different steps to go through for the effective use of a VLE, which I have simplified into the following diagram, and which (I think) has huge significance strategically for organisations that are trying to get teachers in particular to make better (or at least more) use of the VLE.
Set of steps, which are labelled from bottom to top as; Managing, Designing, Building, RepurposingThe 4 steps are:

1: Managing

Many of the clients that I work for, hire me to design and create the various activities that form the backbone of a course on a VLE. The teachers then become skilled at managing these activities – e.g. pointing the students to quizzes at the appropriate times, moderating and encouraging forum based activities, providing constructive feedback for formative assessment activities etc. These teachers in the main are not involved in the design process, and certainly not the building/creating process.

2: Designing

Once a teacher has worked with and managed activities that has been created by someone else, they start to understand how such activities work, what the important ingredients are, and why and when the activities are used. They can then start to design new activities – this may be sketching out the ideas or concepts on paper, it may be creating source information in Word, PowerPoint or Excel – the information then goes to a learning technologist who turns their ideas and content into the actual activity.

3: Building

The third step is the actual building or creating of the activities, i.e. using the VLE tools to actually create the books, quizzes, assignments, forums etc. from this content.

4: Repurposing

Once someone has become proficient at building activities, they can then start to repurpose existing content, and hand in hand with this, build content in a way that makes it easy to repurpose in the future (either by them or someone else).


Some organisations have a centralised learning technology team, which is great, as they can help teachers gradually work their way up through the steps. When a teacher is new to this area of work, the learning technology team can do the building for them, allowing the teacher to concentrate on managing and designing. Then as the teacher becomes more proficient, they may start to do some or all of the building, and later repurposing.

However, there are many organisations out there that don’t have such a support mechanism, or the team is too small to be able to effectively meet all the building and repurposing needs of the organisation, and this then forces steps 3 and 4 onto the teaching staff, often without them having worked through steps 1 and 2.

I don’t have a magical answer to this problem, as money is tight, and organisations cannot just create large support teams out of nowhere, but if we think about this 4 step model, and identify the necessity for teaching staff to work their way up it, it is possible to rethink a little about how we do things. I have worked with some organisations recently where I have been asked to come in and run training, where I have had a few hours to try and get teachers jumping straight into step 3, and without any central support for the staff once I leave at the end of the day. This is always going to be tough for those staff. What would be really good is, if there is a mechanism where staff can manage some existing content first, then design some basic activities which someone else creates for them, and then they receive the training in how to build/create content etc.

Within FE and HE at the moment, there are huge pushes to get people using learning technology more (and in many cases the VLE)  – and what is very noticeable is the very different approaches that organisations offer in the way of support, and more significantly the different levels of understanding from the decision makers in these organisations.

Creating a YouTube based discussion activity in Moodle

I run a lot of training on effective uses of a VLE (usually Moodle) and one of the easiest activities that I show, is finding a video on YouTube, and then embedding this into a forum activity within the VLE.

The reasons for doing this are:

  1. By embedding the video (rather than simply linking to it) – we remove all the distractions, adverts, etc. that appear on YouTube around the edges.
  2. By adding this as a discussion activity, we ask the students a question – this will focus their attention whilst watching the video, rather than just passively  ‘absorbing’ it.

It doesn’t matter if students don’t actually post their answers to the forum (although useful if they do), as they will still benefit from watching the video with the question in their mind.

The following video goes through the steps of how to embed the video, and the basic settings within a Moodle forum activity.

And if you want to only show a portion of the video you can always identify the exact start and end points that you want to play, by following these instructions:

https://davefoord.wordpress.com/2012/11/20/cropping-a-youtube-video-before-adding-to-moodle/