# 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.

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:

## 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

# 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’.

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.

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

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:

# Changing behaviour with Office 365 and Tablet Devices

Over the last week I have produced 4 posts around ideas of using Office 365 in education:

The idea that I am trying to get across, is that Office 365 isn’t just a slightly newer version of the Office Suite, instead it works in a fundamentally different way, but to get the benefit from these improvements, requires people to behave differently, and with the arrival and (I predict) rapidly increasing number of Microsoft tablets within the education system – and the mobility that these devices offer – getting Office 365 to work properly is going to be key.

The main behaviour change, is reducing the number of different versions of the same file that exist in different.

For example: If I create a Word Document, and save it to ‘My Documents’ on my computer. There is one version of the file. I then email this to a colleague. There is now the version in My Documents, a copy in my sent items, and one in my colleagues inbox – so thats 3 versions. My colleague downloads the file (4 versions) and then makes some edits, saving the file to their network drive (5 versions) they email this back to me (Their sent items and my inbox gives 7 in total) – I Download this (8 versions), and save a copy (with a new name) back to my Documents (9 versions).

So – the very simple example above with me asking 1 colleague to proof read and edit a document has created 9 files without me thinking about it. If I had collaborated with more people this number could quickly run into the hundreds – which makes no sense, there is a risk of ‘old’ versions re-appearing a later date, and the total memory used up on the system (especially if a large file) costs money and slows everything else down.

If I use Office 365 and OneDrive (the new name for SkyDrive) the behaviour would be as follows:

I create a file, and save it to my OneDrive. There is now a version on my computer and a synchronised version ‘in the cloud’, I email a link to this file to my colleague, they edit this via the web interface in the cloud, and let me know when complete. I can check their edits, and if unhappy with them I can use the history option to roll back to an earlier version or discard the changes. Ultimately though – we still have the same file on my Computer and a synchronised version ‘in the cloud’. There are no extra versions loitering in mail systems or download folders.

One of the reasons why this becomes so important with tablet devices, is they rely on files being transferred via wireless – so we need to make the whole process of file management more streamlined. If I am working with a class and I want them all to access an image rich  PowerPoint file for example – for me to ‘push’ that file over the slightly ropey wifi in that classroom to 30 different devices could take 10 minutes or more, which isn’t realistic. For me to use the file sharing mechanism within OneDrive and  the students to access this via the web interface will effectively stream the content, as the students view it, rather than pushing the whole file out up front.

In the previous posts I talked about the collaborative teaching and learning opportunities this brings, and with the extra mobility and battery potential of tablets over laptops or desktops, means we can use these devices in any environment including the sports hall, kitchen, on field trips etc.

As with many of my blog posts – the key to changing these behaviours is an investment in staff development. Because Office has been around for so long, Office training is far less common – there is an assumption (which is an incorrect one) that people don’t need to be taught basic office skills. With Office 365 – there is a huge need to reinvest in training in this area, otherwise its potential will not be unlocked.

At the moment I am working with a company called The Tablet Academy who provide training and consultancy around the use of tablet devices (Apple, Android and Microsoft) in education. If any Schools or Colleges in the UK (including Scotland) purchase 20 or more Microsoft tablets (including ones made by other companies) they are entitled to free training, provided by someone from the Tablet Academy (only downside is the current offer is only for training that is delivered before the 31st March 2014). If you work for a school or college in the UK and have purchased such device recently and your reseller hasn’t mentioned the free training offer then contact the Tablet Academy who will chase this up for you.

# Using Word and Office 365 to streamline assessment feedback

In my last few posts I have been explaining some uses of Office 365 to create collaborative activities using PowerPoint or Excel. In this post, I am going to look at Microsoft Word. Word could be used to create a collaborative learning activity in a similar way to the PowerPoint example, but it is set so that only one person can edit one paragraph at a time – therefore careful consideration is required when designing such an activity. In this example I am going to look at the mechanism of student feedback following assessment and h the collaborative nature can be very useful.

If I look at what might happen in colleges, universities and some schools at the present:

1. Teacher produces an assignment brief and gives this to the learner.
2. Learner completes assignment and hands it in.
3. Teacher marks the work and fills in a feedback sheet, hands work and feedback sheet back to learner.
4. Learner fills in the box on feedback sheet where they reflect on the assignment and their action plan to solve any updates required.
5. At end of term, teacher realises learner hasn’t updated their assignment – contacts learner.
6. Learner has lost feedback sheet so has forgotten what needs doing.
7. Teacher re-issues feedback sheet (luckily they have a copy).
8. Student does updates, hands work back in.
9. Teacher remarks work, fills in anew feedback sheet with the additional feedback and final grade.

Although the above may sound like a tedious drawn out exaggeration, I am sure that many will see similarities with current practices  – and whatever ones system, whether paper and pen or electronic there is almost always a significant amount of files moving from place to another and there is seldom an efficient loop where the students use the tutors feedback to help them with their updates or future assignments.

So here is one suggestion. We create a single Word document that is going to contain all of the information relating to that assignment – and this will be used by both tutor and student. This will contain the brief for the assessment, the list of criteria being covered/assessed, and area for the student to reflect on the assignment, areas for the teacher to give feedback, and space for them to add additional information if the work is referred and needs to be upgraded.

Once this document has been created – all the tutor has to do is to share this with the learner through OneDrive (the new name for SkyDrive). The learner and the tutor are now accessing the same document. If the tutor wants to have a situation where all of the students marked work is returned at the same time, rather than piecemeal – they can quickly remove the students sharing rights, mark all of the work and then re-share it.

One of the key reasons why this technique hasn’t worked in the past, is the verification process needs to see the different versions of the work and the feedback given – which in turn lead to the notion of creating lots of different documents. The beauty of using Office 365 and OneDrive is there is a built in history and version mechanism.

With this you can see any previous versions including who made the changes and you can restore or download any of the versions at a later date if required by an IV or EV.

Having one file to deal with rather than lots of files is easier for the tutor and the student to manage. There are less chances of error due to people using the wrong version of those files, and from a teaching and learning perspective having all of the information in one place for the student is far more likely for them to reflect on the feedback and change their behaviour as a result.

I hope that as organisations start to use Office 365 more and more, there is a real effort for people to think about what they are doing and why – and how these technical advancements can make a huge difference to our overall efficiency and effectiveness.

# Using Excel and Office 365 to create learning activities

In my previous posts I have:

In this post I am going to look at using Excel to create a collaborative learning activity.

Many people struggle (some are scared of it) with Excel which is a shame as it can be a superbly powerful learning tool – and it can be used in any teaching area not just maths, accountancy etc. There are many possible uses that I could list here, but I will stick with a couple of simple examples.

If I was teaching research methods or statistics, and wanted to investigate the idea of 2 sets of data and what the correlation is between them, then a simple way to do this is for each student to work out their height (m) and their shoe size (European) record these in a table, then everyone plots a scatter graph of everyones results – and we look at the line of best fit, standard deviation etc. Yes the constructing of a graph on paper with a ruler is an important skill to learn, but on this occasion I want to focus on the way that the correlation changes as more points are recorded, and for that I want to speed up the plotting process, so I am going to use Excel to help me.

So I have created  a simple spreadsheet which has a data entry table – I have identified each student with the letters A-K, and I have colour coded the area they need to enter their data into with a green shading. I have unlocked these cells and then protected the whole sheet, so when the learners add their data, they cannot accidentally alter or delete any of the workings. On the right you will notice that I have pre-created a graph that will plot the data as it is added, and then underneath the table I have created some simple statistical functions to identify mean, mode, median etc of the data as it is entered.

So having created the above sheet, all I need to do is save it to my OneDrive (new name for SkyDrive) – share it with my learners (see previous post), and then allocate each learner a letter between A-K. They edit the sheet in the web browser, entering the 2 pieces of information against their letter. All of the students will be editing the same resource at the same time, so they will see the graph and the numbers change in real time as the amount of data added grows. The beauty of this is that by speeding up the capturing and plotting process, I can spend more time helping the students to understand the significance of what the graph and the numbers mean, and I could reuse the same template to add my own dummy data to show the effect of anomalies, different types of correlation etc.

Any form of class experiment which involves the capturing and sharing of class data can be achieved with this method. If the data entry is more complex than my example, then you could create a sheet within the workbook for one student, then duplicate this lots of times so each student has their own sheet.

Another useful feature is that it is possible to embed sections of the file (e.g. the results table, or the final chart) back into a piece of web space (e.g. the VLE, or a blog) – so below is an example of the final chart – as the file is used and data is entered this will automatically update.

With the above example all students could see (and potentially alter) all other student’s entries into the resource. This may not be desirable – e.g. if you are capturing sensitive data (e.g. if working out Body Mass Index and asking students for their Weight and Height) or you could ask students to feedback what they have learnt, what they have found difficult, what they would like to recover in a revision session etc.

What you could do is create a single Excel file asking for the data that you want. Once happy with this, you duplicate that file however many times you have students. You then share each file with the individual student. This may sound complex but is a lot easier and quicker than you would imagine. You now have a mechanism for students to give individual feedback to you – you as the teacher can see all of the files, so easy for you to see the information – and if you are really keen, then you could create an ‘overview file’ which uses simple excel formula to pull the data from each students file into a single dashboard type file. This last suggestion doesn’t update the data live as it is entered, but everytime that the file is opened it will pull through the latest data at that point.

If we are using Excel to create learning objects then it is beneficial to make the appearance of the file as tidy and uncluttered as possible – which I have discussed in a previous blog post – https://davefoord.wordpress.com/2010/05/19/simple-formatting-tips-in-excel-to-improve-quality-of-learning-materials/

In my next post in this series I will give an example of using Word to create a collaborative activity.

# Adding a file into a Moodle 2 content area

Many people that use Moodle will know how to add files directly into a Moodle course, which works fine for some situations, but there is a risk that one ends up with a long list of files appearing on the course page, without guidance to the learners as to what to do with them.

Personally I hate seeing Moodle (or any other VLE system) courses that look like this, as it doesn’t make for good teaching and learning (we don’t usually go into a teaching session, give the students 3 or 4 handouts, and then let them get on with it – we usually explain and talk to the learners what we want them to do with the resources).

So – rather than uploading files in a long list – what I like to do is to add a narration around the files, explaining to the learners what I want them to do with each of the files or resources that I am sharing with them. For this I need to choose a Moodle resource or activity type. If it is a very simple topic, I may add a ‘page’, other times I add a ‘book’ (which allows me to have multiple pages within the resource), or I could use a discussion ‘forum’, ‘lesson’, ‘Quiz’, or ‘assignment’. As long as whatever I choose gives me the WYSIWYG editor (the row of icons that looks a bit like a word processor), then I can use this technique.

The following video will take you through the process:

Part of the beauty of this technique, is it becomes very easy to move content around within the Moodle course – in that if I want to re-arrange my teaching order, I may have 1 link for the topic (which itself links to 4 or 5 other files) – I only have to move this one link and the files go with it, rather than having to move the 4 or 5 files in turn.

Another place where I use this technique a lot is with the assignment tool. If I want students to submit work through Moodle, and I am giving them an assignment brief (e.g. in the form of a word document) I can create an assignment within Moodle. Add the link to the assignment brief and any other required documents into the description, and then the learners are accessing the brief in the same place that they will be submitting their work – which is much neater than putting the briefs in one place and them submitting their work in another.

# Why the Microsoft Surface Tablet will be a major player in education

In my years as a teacher and then as a freelance consultant/trainer I have been very much at the forefront of the use of technology in education, and I have always been excited by the potential that effective use of technology can bring to education, but nothing has been as exciting as the potential that tablet devices brings to learning, and in particular Apple’s iPad – which has been designed beautifully, and is really easy to use, so when I run staff development in this area, I don’t spend most of the time talking about which buttons to press – instead I can focus most of my efforts into the pedagogic considerations of the training, and that is a very liberating feeling.

For the last few months I have been working with a company called ‘The Tablet Academy‘ (formerly The iPad Academy) and I have been going into schools and running iPad training – but now that the company has changed its name, they are also offering support and training for Android and Microsoft devices, and as such I have been experimenting with the basic Microsoft Surface RT device – and even though it has received some negative press, I think it (and its successors) are going to make a huge impact on education.

If you compare an iPad alongside the Surface, then the iPad is going to win in almost all areas – it is built better, there are more apps, the battery seems to last longer, it is more intuitive etc. however  if we compared a new iPad to an iPad 1 (which is the best way of thinking of the Surface) then the gap between the 2 was huge, so we have to take that into consideration as well. The Surface has some key considerations which the iPad lacks and that is the purpose of this blog post:

• Many schools and educational organisations, have IT support systems which have been built around the Microsoft model (rightly or wrongly), so for them adopting Apple’s iPad hasn’t been an option. The Surface will just slot into their existing mechanism – and that makes it a possibility for lots of organisations who have so far closed the door on tablet technology.
• I let my kids use both my iPad and the Surface to see how they got on. I expected that they would hate the Surface (having used the iPad for much longer) but they didn’t – in some respects they preferred it. As my 9 year old quoted – “The Tablet is really clever, because you can use it with the keyboard and it is just like a computer, then when you take the keyboard off it turns into an iPad*”. As a device for my kids to do their homework, it is so much easier to use than the standard computer, as it fires up quickly, we can use it in any room in the house, it easily connects to our printers, and it has what the kids need for most of their work which is the internet, and access to office tools such as Word and PowerPoint.
• Personally I hate flash, and always have done, and it is a technology that is well past its best before date – but there is a huge quantity of legacy material produced in flash especially within education. It is well known that the iPad doesn’t support flash, and Android doesn’t really (although there are work arounds for both). For the moment Microsoft does flash no problem. Many schools have entire maths and science departments based around the use of flash based resources, and if these schools have gone down an iPad route – they are finding this tough.
• The cost of the Surface is significantly less than the iPad or Android equivalents. Over the summer of 2013, the surface could be bought for £133 + VAT, which is a much easier number to work with when buying potentially tens, hundreds or thousands of these devices.
• Although the Surface may not have the wonderfully creative apps that the iPad has, it does have Microsoft Office – which gives us Word, Excel, PowerPoint, (and OneNote) – which are still the main tools used by many educators and students. Although I love my iPad, and I use a mac as well as a PC, and a mixture of Office, Open Office and iWork – when I want to do serious office based work, I still revert back to the PC as I find the Office suite works better for me than the others.
• The surface is only going to get better. If we look at how the iPad has evolved in a few generations, then in a few years time the Microsoft devices should be much closer in performance. The iPad seems to have reached its own plateau – whereas Microsoft is only just starting.

I think the arrival of the Surface tablet is a very positive thing for education – there are now 3 viable options for education (iPad, Android and Microsoft) and choice has to be good – people can choose what is best for their situation, and the competition should keep all 3 providers on their toes, and prices competitive.

*Obviously the Surface doesn’t actually turn into an iPad – but these were the exact words of a 9 year old, and their perception on technology.

# Printing a YouTube video and it’s uses in education

This may sound like a daft title for a blog post, but it is possible to easily get an export of still images taken at regular intervals from any YouTube video, to create a storyboard of that video. These can either be printed or easily saved as a PDF, and then used electronically.

I don’t want to claim any credit for discovering or developing the technique – that needs to go to Amit Agarwal, who explains the technique on his blogpost ‘Do you want to print a YouTube video?

I am not sure what the legal issues are regarding copyright and this technique, but assuming that this is OK legally, then this simple technique could have a few really smart educational uses.

1. As someone with a sports background, my first instinct was to use this for movement analysis, but the gap between the frames is too great for this to be realistic – but this could be used in a sports setting for crude notational analysis. E.g. if watching a game of say netball – you could count how many frames is team A in possession, how many frames is team B in possession, then work out the ratio, and you have a rough gauge of possession – compare this to the final score line and see if there is a correlation?
2. In a subject like marketing, you could look at the output from a companies advert, and analyse how much of the 30 seconds is spent doing different things e.g. showing the product, showing the prize, repeating a key message etc.
3. In teacher training, if a trainee teacher is filmed completing a microteach – this can be uploaded to YouTube as a private video (so no-one else sees it) you can then create the storyboard of printed images, and then analyse how their time is spent – e.g. how many frames are they writing on the board (with their back turned), how many frames are the students doing something etc. This could be a very effective tool – as counting frames on a piece of paper is much quicker and easier than trying to do the same with complex timings and starting and stopping clocks etc.
4. In media studies – you can analyse the different types of shot (close up, mid shot, scene setting etc.) that are used in a sequence, and what effect this has on the message being conveyed.

Although in general I often like to move away from paper, and converting a media rich resource such as a video into a less rich image, seems to be a backward step, I think the ease of this technique and the power that it brings to carry out a basic analysis of the video is superb, and would be an excellent teaching activity.

# PowerPoint doesn’t have to be passive

I recently had a heated discussion where someone was demonising PowerPoint because of the negative impact he thought it was having on education due to the often passive nature of its use, and it is true the vast majority of PowerPoint use within education would probably fall into the category of poor, with some being good, and a small percentage being excellent. In fact one of the things that persuaded me back in 2006 to go freelance, was in the space of a few weeks, I sat through 3 awful passive PowerPoint presentations about the importance of and how to do active learning.

But it is not the technologies fault – the technology is designed to do a job, and it does the job it is designed to do well. What is at fault is the people using it badly, and for that we need to go back to identify why, and it usually comes down to poor CPD for staff, and low expectations of what PowerPoint can do, which isn’t helped by many senior people in education standing up at conferences and the like and delivering appalling presentations.

In my early years of teaching, as I migrated from the then staple diet of death by OHT (Over Head Transparency) to using PowerPoint, my first attempts at PowerPoint were I am afraid what I would classify now as Death by PowerPoint, but I very quickly had one of those light bulb moments – I made a decision to never do death by PowerPoint again. Once I had made that decision everything else followed easily. I (like many other teachers) know what death by powerpoint looks like – so if I know what it looks like, if I am doing something that is heading that way, I don’t do it – I do something else. The key to me was bringing back the active elements of learning – getting the learners to do something, rather than just look at a load of pre-prepared bullet points on the screen that I talk about and expect learning to take place.

I worked on a principle of breaking my sessions down into smaller chunks of time, usually about 10-15 minutes. So I would talk for a bit, they would do for a bit, I would talk again, they would do something different, we would have a class discussion etc. It was this idea that lead me to creating countdown timers for PowerPoint which helped me manage the time for the different elements of active learning. I then discovered a really wonderful tool of the editable text box, allowing me to capture notes during a session, as part of a discussion activity or carrying out a ‘for’ and ‘against’ analysis. This saved me huge amounts of preparation time and hugely improved the activeness of the session.

I then used hyperlinking to create non-linear presentations, which has an array of uses and can be used to create some very effective learner directed resources, and there are many other things that I have done, and still do, all of which is designed to make the learning process active.

Going back to my opening statement of this post, the person I was discussing with, was all for promoting Prezi, which I don’t have a problem with as such (it doesn’t do anything for me, but I am a high level PowerPoint user) – but the issue is the same, unless staff have proper CPD and support we just get death by Prezi rather than Death by PowerPoint (only with Prezi you can get a bit of sea sickness thrown in for good measure).

When I first started working as a freelance trainer, a lot of the training that I ran was PowerPoint related. Over the years the amount of PowerPoint training I run has dwindled – I think many see it is ‘old hat’ and not needing training, which I wish was the case, but whilst I keep seeing lots of really bad PowerPoint presentations, I am very aware that there is still a need for teaching staff at all levels of education to have good quality PowerPoint training.

I am redeveloping some of my PowerPoint training sessions, one of which is titled ‘Making PowerPoint Active not Passive’ – which is introduced in the following video.

For further information visit http://www.a6training.co.uk/PowerPointActive.php

If you are interested in high quality PowerPoint training, that can be (and has been) delivered to all levels from Nursery through to HE , then visit http://www.a6training.co.uk/ for details.

YouTube is a wonderful resource, it works on just about all internet enabled devices, it hardly ever goes wrong, it is easy to use and although there is a lot of low quality rubbish on there (in my opinion), there is also huge amounts of really useful high quality videos that we can use in education to enhance our teaching and learning practices.

A feature of YouTube that many don’t know about, is the auto-captioning option – in other words YouTube creates a transcript of the video without you having to do anything. If you are watching a video on the YouTube page and you want to see the captions, then there is a button below the video (currently to the right of the where it says ‘add to’) which is the transcript button – this brings up the transcript as a timeline below the videos and automatically advances with the video. This can be great for learners that have a disability (e.g. are deaf), but can also be really useful to find a key point within a video.

For example I often use short sections of the excellent TED talk video of Ken Robinson talking about schools killing creativity. If I want to locate a certain section within that video, I use the automatic captions that appear below it to locate the section that I want.

Because the transcripts are computer generated, they do contain errors – and depending on the clarity of the voice and the background noise of the video will determine the accuracy of the transcript. For some reason my voice never does well with automated speech to text systems, including YouTube.