• ## Email Subscription

Join 1,639 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

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

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:

## Easily create a Venn diagram drag and drop activity in Moodle

There is an excellent plugin for the Moodle VLE called ‘Drag and Drop Markers‘ which allows someone to create quiz questions, where the learners have to drag and drop markers onto an uploaded image. This can be used to name parts of an image (e.g. bones in the skeleton, or geological features of a glacier), or to create effective categorisation questions. One of my favourite question types, is where the learners take pieces of information, and categorise these by placing them in the correct position on a Venn diagram.

To create this from scratch, would be extremely time consuming, so to make my life easier, I have used PowerPoint to create templates for myself, meaning that I can now create these questions in a matter of minutes, rather than hours, and I will release these to the wider community over the coming days and weeks (as I get time to tidy them up etc.) The first such template for release, is for a 3 circle Venn diagram categorisation activity.

The basic principle is:

1. Name the 3 circles with the correct titles.
2. Save the slide as an image.
3. Upload this to the Moodle quiz question.
4. Identify what markers you want to use.
5. Identify which of the 8 possible drop zones is correct for each marker.
6. Copy and paste the coordinates for each dropzone into Moodle.

This takes a matter of minutes to do, and allows someone to create challenging and more effective questions, as part of the formative assessment process.

http://www.a6training.co.uk/resources/3CircleVennDiagramActivityForMoodle.pptx

And a video explaining how to use this is as follows:

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:

## Adding a date picker to Excel

As a huge user of Excel, I am often entering dates into cells. Different versions of Microsoft have had date pickers as an optional add on, but they haven’t always worked for me with all versions or consistently.

I have however just discovered this free tool:

Which is created by Sam Radakovitz , which does exactly what I want.

Once set up, if a cell in Excel is set as a date, clicking on that cell, will bring up a small calendar icon to the right of the cell:

Which when clicked on, will bring up the calendar tool, making it easier to enter dates, and not have to worry about the correct format, or whether the spreadsheet has been set up in UK or USA format.

F

Full credit to Sam for this one, I am just blogging about this mainly for my own benefit so that I can relocate this in the future when I change computers.

## Spreadsheets: How to Sort Data Onto Sub Sheets based on values in a given column

One of the most popular posts on this blog, is one I published back in 2011, titled How to automatically pull data between different google spreadsheets  I am often asked by people, is it possible to filter the data based on the value in a column, before pulling the data across?

I have created a video which shows a technique whereby data is filtered internally within a workbook, so data is pulled onto subsequent sheets, based on values in a certain column. In this example I am using a class of students, and all the grade A students are copied onto a sheet called “A”, all the grade B students are copied onto a sheet called “B” etc. This principle could easily be used to organise a list of sales by sales rep, or by region.

The video is about 13 minutes long, but well worth watching, if you are interested in this technique.

For this example I have used Excel, but this would also work with other spreadsheet systems such as OpenOffice or Google Sheets.

The file used in the video example can be downloaded here, if you want to see or copy the formulas used.

Spreadsheet – sort data onto new sheets Shared

The mechanism also uses a technique to display the sheet name in a cell in the spreadsheet. More details on this can be found at https://davefoord.wordpress.com/2015/04/16/how-to-display-the-sheet-name-in-a-cell-in-an-excel-spreadsheet/

I hope that this helps people to make better use of Spreadsheets, whether it is in education, work, or for personal use.

Edit (22/6/2017) – please note that since writing this post, the company importsheet.com has been renamed as sheetgo.com

In November 2011, I wrote a blog post titled ‘How to automatically pull data between different Google Spreadsheets‘ – which was based on a feature called ‘ImportRange’. Although nearly 5 years old, this particular blog post is one of my most frequented and certainly the most commented on post that I have ever written.

I have recently been introduced to a new add on feature called ‘Import Sheet’ – which can be found at https://importsheet.com/ – and this does exactly what it says on the tin. It allows you to easily import (or export) a sheet from one file to another. So for example, I use google sheets to log the work that I do for each of my clients, with each client having a separate workbook (this means that I can share that workbook with them, without them seeing other contracts that I work on). I can then have a master dashboard, which imports copies of these sheets into a single location, and using simple functions such as the = function, I can then pull key data out of each of the sheets into my dashboard. Using Importsheet rather than the ImportRange feature is much easier, quicker and less likely to have problems.

The add on is a commercial tool, that does have a pricing plan, however the free version does what most people will want. Obviously I have no idea how the pricing plan may change in the future – they may choose to get rid, or reduce the functionality of the free version, so I wouldn’t suggest that people invest lots of time creating high stakes activity with this add on (unless they are prepared to pay in the future) – but certainly for the moment, this looks rather neat.

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

or

$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:

https://www.codecogs.com/latex/eqneditor.php

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:

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.