Conditional formatting to change colours dependent upon varient?

[Gmunro]

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

[ChemistB]

You have (in your example) 3a as a baseline, then 4b is red, 5b orange, 5a blue and 7c purple (looks more like brick red)
I calculate 4b is red, 5b orange (so far so good), 5a also orange (10-4 = 6) and 7c purple (14-4 =10)

I used the following conditional formatting equations (in this order so enter in the opposite order and check Stop if true)
Select range of C4:N7......
=IF(LEN(C4)>1,INDEX($P$4:$P$23,MATCH(C4,$Q$4:$Q$23,0))-INDEX($P$4:$P$23,MATCH($B4,$Q$4:$Q$23,0))<5) Red
=IF(LEN(C4)>1,INDEX($P$4:$P$23,MATCH(C4,$Q$4:$Q$23,0))-INDEX($P$4:$P$23,MATCH($B4,$Q$4:$Q$23,0))<7) Orange
=IF(LEN(C4)>1,INDEX($P$4:$P$23,MATCH(C4,$Q$4:$Q$23,0))-INDEX($P$4:$P$23,MATCH($B4,$Q$4:$Q$23,0))<9) Blue
=IF(LEN(C4)>1,INDEX($P$4:$P$23,MATCH(C4,$Q$4:$Q$23,0))-INDEX($P$4:$P$23,MATCH($B4,$Q$4:$Q$23,0))>8) Purple

Does that work for you?

[Gmunro]

Hi ChemistB

Many many thanks, this is nearly there, the only issue is it appears to colour the cell that is two to the right, instead of colouring the cell that the data was entered into. I cannot figure out why this is happenning, any thoughts?

Gmmunro

[ChemistB]

When I set the conditional formatting, I selected C4:N7. I am guessing you chose E4:N7? Go to Conditional formatting> Manage rules and see if you can change it.