Progress tracker Example.xlsx

Hi All

Please could you help with the following spreadsheet. I am atempting to show the progress made by my students through a colour system that links with a grading system. So when a certain end of year grade is formulated, the said cell will change colour to represent the progress made by the student.



My grades follow a number & letter (abc) scale, less than 3c the lowest & 8a being the highest (3c,3b,3a,4c,4b,4a,5c,5b etc)


A one letter movement counts as a rise so 3c to 3b is 1 level rise, 3c to 4c is a 3 level rise.


I want to indicate the colour of Progress based on the following:



< 5 levels = less than expected (want the cell to turn RED)
5-6 levels = expected (want the cell to turn Orange)
7-8 levels = good (want the cell to turn Blue)
9+ sub levels = outstanding (want the cell to turn Purple)

The level of rise is naturally determined by the starting point at all times and so I need the formula to use the 'baseline test' column as it point of reference at all times.


I think I have explained all of that, you help would be amazing on this issue as it would become an invaluable piece of work for my students.

Many thanks



Graeme