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

    Join 1,630 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:



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


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:


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:


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



Creating equations in online environments

I am currently working on a project, where I am creating web based resources (using Moodle) to teach maths. As part of this process, I need to create properly laid out formulas (or formulae if you prefer the alternate acceptable plural of formula). We are using what is becoming a widely accepted standard of MathJax which in turn supports the use of something called LaTex to create the desired formulas. So for example if I wanted to create something that looked like:

y = 3x^{2}+5x+\frac{2}{3}

I would enter this into the editor using the code:

$$y = 3x^{2}+5x+\frac{2}{3}$$


\[y = 3x^{2}+5x+\frac{2}{3}\]

At first I started to learn the exact syntax and would translate what I wanted into either of the codes above. This proved to be both time consuming and prone to mistakes. Then I discovered an excellent website that helps me do this:


This website gives me a box into which I construct the equation that I want – above the box is a huge suite of grey buttons which each represent a different mathematical function or options. These take a bit of time to learn, but quite quickly one gets the hang of this, and using the buttons and adding the numbers / letters that you require you can quite quickly create the desired formula that you want. Underneath the white box, your formula is displayed as it will appear, so it is possible to see what you are doing, and check that this is correct.

Once you are happy with what you have created, at the bottom of the screen (in a cream coloured box) is the option to choose the export style that you want – so if you need LaTex, you choose that, if embedding into WordPress, you choose WordPress etc. You then copy the code beneath this, and paste into the editor of whatever you are using.

One of the facilities within the editor, is to create correctly aligned equations:

Image of an inline equation

Which in LaTex is created with the code:

\[\begin{align*} x+3 &= 7\\ x &= 7-3\\ x &= 4 \end{align*}\]

This is very hard to manually write out, but quite easy using the codecogs website. The button for this, is the bottom right button on toolbar (letters n and r in brackets) – then under that is a button that has “y=…” as the text, and when you hover over it, it tells you that it is the align tool.

For anyone who is using mathematical formulas regularly this is a really neat tool.



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:



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.


#FELTAG – buying or not buying resources conclusion

This is the 4th and last post in a series looking at the issue of should we buy off the shelf resources or produce resources in house. The previous posts have been:

  1. #FELTAG – to buy or not buy resources?
  2. #FELTAG – Considerations when buying off the shelf resources
  3. #FELTAG – Considerations if not buying off the shelf resources

My intention in this series is to provide the decision makers in organisations with ideas and considerations to help them make an informed decision in this area of work – a decision that is not easy or straight forward, yet the consequences of making the wrong decision are huge.

Every organisation is different, so there certainly isn’t a one size fits all answer. What is right for one, will be totally inappropriate for another, and when a decision is made it doesn’t have to be a blanket whole organisation decision, It may be that for certain teams it is better to produce resources in house; because they have the skill to do so, and the quality of the commercial options in that area isn’t great. Then other teams, may choose to buy all or some of their resources in.

It also isn’t necessary to buy all resources from the same provider – yes they may give you a huge discount for buying a full suite of resources across all subjects, and yes it would be easily technically and managerially to deal with one set rather than lots of sets – but if the resources for certain subjects within the suite aren’t good enough, then they either won’t be used, or will be used badly.

It may also be necessary to change tactic part way through, e.g. you may choose to produce resources in house for one particular course, but part way through you realise it is just too difficult and isn’t working, and you decide to buy in. Or you may choose to buy resources in – but once you have done so, you realise that you could do a better job in house, so you start to develop your own – which you then phase in as the bought ones become obsolete (e.g. at the end of the year on an annual subscription).

It is also imperative to shop around – don’t just jump straight into the ones that are endorsed by the awarding body – especially as some of the awarding bodies are also publishing companies – their endorsement is not always a sign of their real value and quality.

Whatever decisions are made – many factors have to be balanced as follows:

  • Financial – buying in and producing in house both cost money.
  • Quality – buying off the shelf, on the surface should be higher quality as far as resources go – but doesn’t mean the overall quality of the teaching and learning will be higher (just like buying really expensive glossy books, does not substitute quality teaching).
  • Time – Buying in is certainly the quicker option, but if the resources aren’t appropriate or don’t fit the organisations systems, learning how to use them effectively may take additional time.
  • CPD – producing resources in house, becomes part of the CPD process, so brings an additional benefit that you don’t get from buying in.
  • There is a lot out there for free – there is a huge amount of freely available materials and assets that can be used. So in some areas, buying off the shelf resources is relatively expensive, as you could easily produce something similar in house very cheaply. In other areas where there is less freely available content, buying resources is better value pound for pound.

Whatever choice is made – it mustn’t be rushed, it has to be balanced, and all relevant parties need to be involved in the decision making process. If the right decisions are made, it is possible to provide a really high quality and cost effective learning experience.

Image of a pair of balance scales


Whereas I welcome comments on my blog posts, please don’t use this blog post as a way to either promote or criticise any particular companies or products. Any such comments I will delete.

Image source: https://www.flickr.com/photos/julia_manzerova/4748112382/


#FELTAG – Considerations if not buying off the shelf resources

This is the 3rd post in a series on “FELTAG – To buy or not to buy resources“. In my last post, I looked at the advantages and disadvantages of buying off the shelf resources. In this post we will look at the advantages and disadvantages of not buying.

From a simplistic perspective, not buying resources is an easy option, as management can just ask teachers to do the extra work in their own time, at no extra cost to the organisation. Although this may seem a simple and convenient solution in this financially difficult time – the result will be low quality teaching and learning, teachers being off work ill, and many good teachers leaving the profession – none of which are good for the organisation long term.

Image of teachers creating resources

Teachers creating resources

If teachers are being expected to create new content, then some time or financial reward for them will need to be found for this to be truly successful – so we shouldn’t look at the ‘Not buying resources’ option as a cheaper solution (as it probably won’t be) – we should make the decision based on the quality aspects and strategic benefits.

Strategically – working with teachers to develop resources, is a very important element of upskilling them to being competent digitally capable practitioners. So any cost invested in the development of resources with or by teachers – isn’t just creating resources but is forming part of the CPD requirement for those staff – if we think about this issue from this perspective alone, financially this becomes much more attractive.

Other benefits are:

  1. Resources will be developed in line with your existing systems, infrastructure, house styles etc. so will ultimately become more embedded than buying off the shelf resources.
  2. Resources will be easier to adapt in line with changes to curricula, subject knowledge, or changes to the devices being used to consume the content.
  3. Resources won’t be as locked down, so will be easier to make more accessible, and adapt easier if required.
  4. Resources can be tailored to the specific location of the organisation – e.g. an organisation teaching catering, can make reference to their own training kitchen. Organisations teaching travel and tourism that are based near the sea, can use resources based on local resorts – this can make a huge difference to learners as they make the transition from fully face to face learning, to blended learning.
  5. With the right amount of support from learning technologists, and high quality staff development – it is possible for a good teacher with average levels of IT ability and a bit of time to generate adequate quality resources that would be comparable or even better than the commercial options (Many of the resources that I have developed with or for organisations are significantly better than the purchasable options).
  6. There are loads of free learning resources or assets out there in terms of OER (Open Education Resources), Creative Commons images, YouTube videos, iTunes courses etc. so creating resources, is not about building everything from scratch – it is about locating, and evaluating existing content – then bringing this together in a sensible way that supports the learner through the journey. If a teacher is creating their own content, I would argue that they should only be creating a maximum of 25% – the other 75% should be free external resources, or adaptations of existing resources used in classroom sessions.
  7. FELTAG is about a whole organisation approach to this area of work. By going down this route, the organisation as a whole will learn and develop and adapt as part of the journey.

Some of the disadvantages are:

  1. For this to be successful this needs to be effectively managed and resourced, which may mean organisations taking a long and hard look at themselves and deciding if they have the management ability to do this – and if they don’t, how do they change the personnel so they can.
  2. Developing resources takes time. When organisations were looking down the barrel of the gun trying to get things done by September 2015, time wasn’t a luxury at their disposal – the dropping of the 10% online being mandatory has given organisations more time (which I think is good) – but they still need to plan carefully, how and when and what order to develop courses. One option is for teachers to be given up front time to develop online resources/activities etc. before the course starts – another option is for the teacher to be given time as the course is running, and as long as they stay ahead of the students will be OK. Either way, you don’t often get things completely right the first time – you need to create something, use it with the learners, evaluate how it went, adapt accordingly etc. I believe that it takes about 3 iterations of this cycle before online elements of courses get to a really good standard.
  3. Some teachers don’t have the skills required, and never will – this then creates a problem for management – do they allow those staff to go to pastures new? or do they carry on putting a greater workload on the teachers that can?
  4. Creating resources in house requires an effective support team. Many organisations at the moment don’t have this (or enough staff in these teams) – and especially for smaller organisations, bringing in staff with the right range of skills can be challenging.

If organisations choose to create resources in house, they can help themselves by thinking of the procedure up front. e.g. who will do the work? If support teams are required, how are they managed and their time charged to the individual teams? What quality assurance procedures or processes will be in place, and most importantly who will manage the process for each different team or course?

Whereas I welcome comments on my blog posts, please don’t use this blog post as a way to either promote or criticise any particular companies or products. Any such comments I will delete.

The next and final blog post in this series, will be summarising the considerations covered in the previous 3 posts.

Image Source: http://www.morguefile.com/archive/display/875771