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

 

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/

Blended learning is not ‘new’ – and calling it so is damaging

I was recently in a conversation about blended learning with a senior manager of a college, who kept referring to blended learning as being ‘new’, or this ‘new way of working’ or this ‘new approach’. At first I accepted these slips of the tongue, as referring to the fact that it was new to him and possibly his organisation, but as the conversation developed, I realised that he was seeing blended learning as being new in general, which of course it isn’t.

I don’t want to try and pinpoint the exact point in history that blended learning started (many other people have done that) – as that isn’t the purpose of this post, the point here is that it clearly isn’t new. Although we didn’t call it blended learning at the time, I was doing a form of blended learning about 16 years ago. In 2007 I started working on the excellent AASE programme at Loughborough College – which was and still is a hugely successful blended learning FE programme, and I have been working on blended learning projects almost exclusively ever since. So for me that is at least a decade, which in education and technology terms, is a very long time, and certainly not ‘new’.

Going back to the conversation with the senior manager. He was clearly scared of this way of working, and a way of coping with that fear, was to somehow make it sound that this was an untested, or experimental way of working that hadn’t been proven, and in doing so justified his lateness when arriving at the party. But the problem here, is that this inaccurate fear, and his overuse of the word ‘new’ (I don’t think he was conscious how often he did it) – is going to have a very negative effect on his organisation. If he has to stand up and inspire teachers to change their ways of working, he will struggle to do so, as he hasn’t even convinced or inspired himself.

Image of 2 characters, one on an upward arrow, the other on a downward arrowI also expect that this situation isn’t isolated to him or his organisation, and is quite widespread through education in the UK, and I predict is an issue that may take quite a few more years to go away. What I do expect to happen is a greater gap between those organisations that do and those that don’t, as the organisations with senior managers who simply don’t get it, being held further and further behind, whilst others progress into the future.

I cannot offer any magical solutions to this problem, as I feel it is possibly ingrained within the ‘DNA’ of the individuals – I just hope that over time enough people come into the senior positions that do get these ideas and notions, that there can be the widespread cultural change to stop treating things that have been around for years as ‘new’.


Image Source: https://pixabay.com/en/white-male-3d-model-isolated-3d-2064871/

The future of flexible learning requires flexible working

A couple of weeks ago in the UK, the clocks changed. Most people in the UK rejoice at the October clock change as they get an extra hour in bed on a Sunday morning. Personally I rejoiced as it meant I could get an extra hour of work done on the Sunday morning. I appreciate that this makes me sound either very sad, or an egotistical workaholic – but one of the beauties of my work is that I have the ability to work flexibly, and can therefore choose what hours I work. I regularly get up early and do a couple of hours of work before breakfast, even at weekends, but then I take time off during the day which is much more useful to me and my family life. The key here though is about choice. Most weeks I take at least one half day ‘off’ sometimes more, and I try not to work too much during school holidays, so have about 12 weeks holiday a year – yes my income is reduced significantly as a result, but that is all part of the choice process that comes with flexible learning. As part of my work I support many clients both within the UK and globally – and subsequently, I regularly have to work at unusual times to account for global time variations, again this is part of the flexibility that my work requires and I enjoy.

Image of someone working on a computer outside on a bench with a cup of coffeeSo – coming back to the title of this post. We have identified for many years the advantages that flexible learning brings to the learners, but we don’t appear to have caught up yet, that to truly support flexible learning, requires better flexible working from the teachers. I regularly speak with senior managers in organisations about things such as blended learning, and often discuss options such as providing tuition outside of normal working hours – but I am often given the excuse that ‘Teachers won’t want to work weekends or evenings’. This is clearly nonsense, as any teacher or former teacher (like myself) will tell you, that they have to work weekends and evenings anyway to keep up with the planning and marking, as part of their job. Some teachers would welcome the option to work outside of normal hours to formalise the work they are doing anyway at those times.

What the real problem is, that many parts of education are still stuck in the factory/office mentality of working 9 to 5, Monday to Friday. There is also an inherent and sad distrust, that if teachers were given more flexibility, they would somehow abuse this and not do the work (which is again nonsense) if they are not on the premises where they can be checked up on, these two issues are genuine barriers to organisations taking flexible learning seriously.

So – what do I propose? If a teacher is being expected to support a significant numbers of learners that are studying flexibly (by whichever means), I think that teacher should have one day a week where they have the option to not come into work on that day. In other words, the timetable is constructed such that, that teacher has no timetabled classes on that day, giving them the freedom to come in and work in the office, or stay at home and work, or do something totally different, and then work flexibly in the evening or at the weekend. I wouldn’t have any sort of ‘clocking in’ system – I would simply trust the teachers (who by default is already doing more hours in a week than they are paid for), for them to use their professional judgement, as to what needs doing, how much needs doing and by when.

If we take this model further – if an organisation sets up genuine hot-desking in an office (and I have worked at a college that has done this successfully) – you can get away with significantly smaller and more cost effective staff rooms, as you don’t need to provide a desk for every single person, only for that desk to be unused for about 70% of the week whilst they are teaching. Rather than sticking desktop computers onto the desks; you provide teachers with laptops, and have docking stations on the desks so they can use a proper mouse, keyboard and monitor when there. Get rid of the landline phones, and replace these with mobile phones for teachers (which I have blogged about previously – https://davefoord.wordpress.com/2014/06/19/if-you-are-serious-about-blended-learning-give-teachers-a-mobile-phone/)

This does require a major culture shift within organisations, and going back to the issue of choice, some teachers won’t want increased flexibility, which is fine, but for those that do – then now is the time to explore this way of working. If done well it will reduce costs, improve quality, keep teachers happier (which should reduce turn over rate of staff), and should increase the satisfaction of the learners.


Image Source: https://pixabay.com/en/laptop-notebook-work-keyboard-2443739/

Stop blaming the tools, and invest in CPD

There seems to be a recurring theme in education, where tools are blamed for poor practices. PowerPoint, Interactive Whiteboards, Tablet devices and various VLEs have all fallen foul of this phenomenon, and yes the tools themselves may contribute, but in most cases it is the way that they are used that is the problem.

If I use a sporting analogy – if I play cricket and I get out cheaply bowled (which is sadly too common an occurrence) it is not due to the fact that I have a cheap bat that is over 20 years old – it is due to the fact that I swung the bat and missed the ball. I would never blame my bat for my inability, nor would I head to the local cricket store and spend £200 and expect to suddenly start scoring 100s – I would still get bowled cheaply, just with a more expensive bat for decoration.

Image of computers in a skipGoing back to PowerPoint – the staple presentation giving technology that is used and abused by many, and yes sadly there are many low quality presentations out there – but then you look at some of the things that I (any many others) have done with PowerPoint, and realise that it can be an excellent tool. So what is the difference? Well usually having the time, desire and opportunity to learn how to use it effectively. When I first started working freelance just over 10 years ago, I was regularly running training sessions on the effective use of PowerPoint – but nowadays, I run very few, as people think it is ‘old-hat’, everyone knows how to use it (which is clearly not the case) and it isn’t seen as fashionable to run this sort of training. People have tried using or encouraging others to use different tools such as Prezi, Keynote, Google slides, Sway etc. but without investment in CPD in these, the same problems will occur. Rather than people creating bad PowerPoints, they just create bad Prezis (which is like a bad PowerPoint, but additional sea sickness thrown in), and so these tools will get blamed for the poor use, and we will switch to the next ‘new’ miracle tool, and around we go again.

We currently have a similar situation appearing within the VLE market. For many years – the two heavy weights were Blackboard and Moodle, but Canvas has arrived on the scene with a bang, and many institutions are switching to it. It’s main selling point is its simplicity of use, which is obviously attractive, but talking to decision makers in organisations that are switching, I am again sensing that people are switching because they are blaming the previous tools, rather than the lack of CPD opportunities about effectively using the tools. My prediction for the future, is there won’t be enough CPD for the use of the new tools, they therefore won’t be used as effectively as they could be, and in 4 or 5 years time, they will switch again.

The decision to change VLE tool, is a huge decision for an organisation to make – there is the cost involved, the disruption, the transferring of existing courses etc. so not a decision that should be made lightly – but my current fear is that people are making the decision for the wrong reason. A more sensible approach would be to invest more in the CPD of your existing tools from the start, so that they can be used effectively, rather than blaming the technology.

So please, can we stop blaming the tools, and focus on the CPD?


Image Source: https://pixabay.com/en/computer-scrap-technology-garbage-2049019/

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: