• ## Email Subscription

Join 1,624 other followers

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

## Adding files to Moodle in multiple formats

In my previous 2 posts I have talked about:

Adding a file into a content area in Moodle 2

Adding file type icons to Moodle

Another consideration when using files within any VLE system, is there is now a strong case to upload content in more than one format to take account of the fact that there are many different systems out there, and not all file types will be accessible from all computers or mobile devices.

e.g. a word document if opened on something like an iPhone or iPad although it will open, the layout and formatting may be changed and may make it unusable. If the word document is only ever going to be read and not interacted with by the learner, then the sensible thing is to save the file as a PDF – which will work on any device and will appear exactly as you want it. If I want the learner to edit the document – then I upload in both Word format and PDF format.

Again PowerPoint does not always work well on a non-microsoft device. If the presentation is very static with no animations or enhancements, then I will save this as a PDF and upload in that format instead. If the PowerPoint does have things like animations then I will upload this as a PowerPoint show – so that those with PowerPoint have the benefit of this functionality, but I will also upload a PDF version immediately below it – the animations won’t work, but they will be able to read the content on the screen – and if the presentation is well designed then they will still be able to access all of the information. Hyperlinks within PowerPoint will still work when exported as a PDF – although is complex hyperlinks have been created from non-rectangular shapes, then these will be converted to rectangles which may reduce the functionality.

This is where using the Adding file type icons to Moodle technique really comes in as it becomes clear to the user which file they want to download.

I have tested this method on various projects and the feedback from the learners has always been really positive.

In my next post, I will talk about another way to add links to files within Moodle.

## Choosing different pins when creating Google Maps

I am not sure quite how (as I am sure it worked when I originally wrote this post) but the method that I describe below doesn’t work completely as described. I am trying to work out how to get round this, as I think this would be a really useful technique. If I can solve this, then I will re-post to this blog and will update this post accordingly.

Sorry for any confusion caused.

I have in the past blogged a couple of times about Google Maps – for example https://davefoord.wordpress.com/2009/09/07/quickly-creating-a-google-map-from-a-spreadsheet-of-data/ gives instructions on how to create a Google Map from a Spreadsheet of data, and https://davefoord.wordpress.com/2010/05/21/creating-a-distance-from-google-map/ explains how you can create a radius diagram from a pin of either road distances or travel times.

With the first of the above techniques – one problem with this is that you often end up with the pins that are plotted being invisible at first – meaning that you have to then edit each one in turn to choose the desired colour pin, but it is possible to add data to the original spreadsheet to automatically plot pins of your choosing.

To do this add a column and title it something like ‘image’ or ‘icon’. Then you need to add against each entry the URL of an image that you want to act as the marker for the point – you could use anything (e.g. your own company logo) as long as it is small in size. Or you can use the pins that Google provide – all we need to do is find out the URL of each of the pins, that you may want, which I have done for you here so

Red

red-dot

Red pushpin

(so all you need to do is copy the URL and change the ending to either red.png, red-dot.png or red-pushpin.png to get the desired shape.

You can then replace the colour ‘red’ with yellow, green, purple, pink, or blue to get different colour pins

We can also have letter markers such as

MarkerA

which has the code of http://www.google.com/mapfiles/markerA.png (and you can change the letter A for any other letter of the alphabet)

If we are creating our starting list in the spreadsheet it is possible to automatically create a different letter for each entry – by using a simple formula in the spreadsheet

And whilst researching for this post, I discovered http://sites.google.com/site/gmapicons/home which has a few other icon URLS in its list.

So having created our spreadsheet of data, and added an image URL against each URL – if we then go to http://www.batchgeo.com/ the site mentioned in my first blog post) – When you get to Step 2 – there is a button for ‘Advanced Options’ – if you choose this, then against ‘Image URL’ choose whatever column header you had in your spreadsheet.

With some clever understanding of Excel and the use of formulas, this could be a very powerful technique for creating maps.