• Dave Foord
  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,637 other followers

  • Dave Foords Twitter

  • Advertisements

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

Strategic considerations if thinking of switching from Moodle to Canvas

Last week I attended a ‘Digital Innovation rOundtable’ meeting in London – this is an informal group of FE providers in the London area that meet regularly to discuss pertinent issues in the are of learning technology. Last week’s topic was around Canvas LMS – which caught my interest hence I decided to attend.

All of the providers at the meeting are either current or past users of Moodle, and 3 have made the change from Moodle to Canvas, and have been very pleased with the results.

The purpose of this post, is for me to reflect on the event and to provide some strategic considerations that need to be included in any decision making before switching VLE. I have met with various senior managers/leaders who have decided to make the switch, but the reasons they give are ‘we have heard it is better’, or ‘The college down the road is using it, so it must be good’, and these are not good reasons to make the switch.

At the meeting last week, many of the attendees were unhappy with their Moodles, but this isn’t the fault of the system, but faults in the way that it has been set up over the years, themed and supported. People were saying that they didn’t like Moodle because it doesn’t work well on mobile devices – when in fact if set up properly, Moodle works really well on mobile devices, and is one of its selling points. Other people complained that everything was too cluttered – but this again is down to the decisions made within the organisation. If a Moodle uses a 2 column theme like Adaptable, or the newer Boost them, then it isn’t cluttered at all.

During one of the presentations from an organisation that has switched, they kept highlighting things in Canvas that cannot be done in Moodle – but in fact most of these things, are things that can be done, and with the Moodles that I support are routinely done as standard, which highlights the problem isn’t with Moodle per se, but with the way that it had been used in that organisation. A lot of emphasis was made on the appearance and layout of Canvas, which for those that haven’t looked at it yet, is quite similar to the Boost theme in Moodle.

Advantages of Canvas over Moodle

  • The main selling point of Canvas is its simplicity – it is easier for staff and students to use, which is obviously a good thing, it has also been designed from the ground up based on the user experience, so is a lot less ‘clunky’ than Moodle (which having evolved over many years organically and by lots of different people , there are a few inconsistencies in the way things are done and the language that is used, which to an average tutor is confusing).

Advantages of Moodle over Canvas

  • Moodle has the potential to do a lot more than Canvas, with the huge number of plugins available, and its constant improvement, it is a far more powerful tool (in the right hands).
  • It is also a lot cheaper than Canvas. A typical sized college in the UK should be able to have Moodle externally hosted in the region of £5,000 – £10,000 depending in the specs, and levels of support etc. Speaking to people at similar sized institutions that are using or looking to use Canvas, they are being quoted in the region of £25,000 – £30,000, so there is a significant difference in cost here. If an organisation spent half of the difference between the 2 on training, external support etc. then they could make their Moodles work really well.

Breaking the decision making process down

Image of 2 characters looking at a signpostOne thing that became apparent at last week’s meeting, was the difference between people’s Moodles. Some are good, some bad, and some down right ugly. If you have a Moodle that is so ugly that people hate using it, it has a huge negative perception, then the decision to switch is going to be a very different one to if you have a bad or good Moodle, where investing a little time and money into what you already have is probably a better option moving forwards.

The first steps when considering whether to switch or not, is to consult as many stakeholders as possible – and for this I mean students, teachers, and whatever learning tech teams you have – find out what they like, don’t like, how they are using it, which features are widely used etc.

Then identify what you as an organisation need both now and in the foreseeable future. Many people are choosing options based on current behaviour – e.g. most teachers are sadly still using Moodle as a file repository for their learners, but as we move forward with the notions of blended learning, we need more than file repositories, and we have to be careful that switching to a ‘simpler’ system, may be a good thing for the current behaviour, but what about the expected and required behaviours in a few years time?

Thinking about the costs

As mentioned earlier, if done properly then Moodle should be the cheaper route to go down, which for FE providers at the moment, has to be a serious factor in the decision making process. When Canvas first appeared in the UK, it was seen by many as a cheaper option than Moodle. That is no longer the case, and what we don’t know is what the pricing will be in the future. I firmly believe that Canvas are not planning on following the Blackboard model, of hiking prices once people are committed to using them, but if in the future the company is bought out by a bigger company that does have different morals/pricing ideology then we cannot rule out this eventuality, and this has to be factored in as a potential risk (even if a small one). With Moodle being totally open source and free, there is no risk of a price hike – it will always be free, and if the hosting companies put up their prices, you simply move to someone else. From a risk management perspective, this is a significant advantage of Moodle.

[Edited on 17/01/2018 – Since initially releasing this post, Dave Perry commented that Canvas is owned by a venture capital company – as per this press release: https://www.instructure.com/news/press-releases/instructure-secures-pre-ipo-series-e-round so the risk of a price hike is slightly higher than I realised, as the venture capital firm is going to expect and demand a return on its investment]

Going back to earlier in the post, most people that are unhappy with their current Moodles, are due to poor decisions being made at various points in the set up and deployment, and probably associated with this, is insufficient money and support to get it right. I foresee that many organisations that got Moodle wrong, will see Canvas as a magic pill, that will solve all their ills, and will make the same mistakes with Canvas’ deployment as they did with Moodle’s deployment – the result will be, in 3 or 4 years time, we will be back again having conversations about the problems with Canvas and thinking about switching to whatever is next around the block.

Whether an organisations stays with Moodle or switches, then there has to be an additional internal investment to get the best out of either tool.

Strategic impact on switching

Something that is often missed during the decision making process, is the impact on the teaching staff. If all the teachers hate Moodle, then you don’t have a problem. However if you have some (even if only a few) who like it, and have used it effectively and over the years have invested significant amounts of time and love and energy into improving their courses, then they are not going to be happy to have to redo all that work again in a new system, and this has to be effectively managed. Thinking about myself as a former teacher and how I would react if this happened to me – I would be furious, and any future work that I do, I would make sure is more portable in case we change ship again, but in doing this I would be creating a weaker experience for my learners. If an organisation does have a few such teachers and decides that they are going to switch, my recommendation would be to set up a ‘super-user’ system. Any teacher can apply for this, you then select a handful of super users (based on their previous uses of Moodle). These are then given a single down payment to work an extra week in the summer holidays to transfer their Moodle courses into Canvas, and to use these as exemplar courses for others in the future.

Conclusion

Canvas and Moodle are both excellent tools, and I hope both will be around for many years to come. If an organisation doesn’t have a VLE or their Moodle is so horrendous then the choice of Canvas is easier. If an organisation has Moodle and is either Bad, OK or Good then the decision to switch needs a lot more thought. My instinct would be to first investigate what can be done with what you already have. I support various clients with their Moodles, and the ones where I have a high level of control, then the Moodle is clean, mobile optimised, has high levels of accessibility, a good user interface, and is a pleasure to use – so it is possible to create what you want with Moodle.

The decision making process about whether to switch or not, needs to be a properly run project in its own right, firstly to identify if to switch or not, and then what next steps to carry out to ensure that the development and deployment of whichever tool is properly managed.

Shameless plug

If an organisation wants an independent external person (who is a teacher by background, not a technical person) to come in and review what you currently have, what you could improve, and help you to make the sorts of decisions detailed above, then please get in touch. I have provided such a service for many providers, who have found the process extremely useful, and for many has resulted in significant cost savings, as well as the obvious quality improvements.


Image source: https://pixabay.com/en/away-fork-decision-waymarks-1020437/

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/

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/

Trimming and Embedding a YouTube Video into PowerPoint

I have blogged many times in the past about things to do with PowerPoint, including how to embed a YouTube video, or how to use TubeChop to embed a YouTube video.

In more recent versions of PowerPoint (2013 and 2016), the ability to embed a YouTube video has been made easier, and the following video will take you through the steps:

Although easier to do than in the past, this technique has been unreliable for some people in some organisations, so I always recommend to people to paste the video’s URL onto the slide somewhere as a live link, so if this doesn’t work, you have the fall back of simply accessing the video via the YouTube website.

This technique is showing how to embed the video – this means you still need access to the Internet when viewing the presentation, and it won’t work if the organisation blocks YouTube.

This technique replaces the older method of using the shockwave flash object, or using TubeChop to trim the video.