Trying to conditionally format columns based on dynamic cell values

Hi legends,
So the school system that I am working in has a scale for student achievement that starts at the letter A (the lowest) and 10 being the highest.
The specific progression scale is annoying as it doesn't follow the standard logic that you would think applies. Here is the full scale:
A, B, C, D, 0.5, F, F.5, 1.0, 1.5, 2.0, 2.5, 3.0, 3.5, 4.0, 4.5, 5.0, 5.5, 6.0, 6.5, 7.0, 7.5, 8.0, 8.5, 9.0, 9.5, 10

What I am trying to get this spreadsheet to do is to conditionally format the numbers in the columns based on the students achievement score and their year level. To explain the scale more, 2.0 is the expected progression point for a student who has finished grade 2. 2.5 is the expected achievement level for a student who is half way through grade 2, 3.0 is the expected for grade 3 and so forth. The letters A,B,C and D refer to students whose level of achievement is below the expected result for any student who is in primary school and usually is representative of a student with a learning difficulty. F is the expected level of a student in their 'Foundation' year of school, and F.5 is half way through grade 1. 0.5 refers to a student who is half way through their 'Foundation' year of schooling, which is why the scale is messy. If you're confused by the explanation have a look in the sheet in the tab where I've outlined it in table form.

I believe I've got the sheet doing what I want it to do for column E, however I haven't been able to find an easy way to copy the same conditional formatting into the other columns without having to individually alter the formulas. I also haven't applied the conditional formatting to the exceptions e.g. if a student is more than 12 months below where it involves an F. An example being a student who is in Foundation at the end of semester 1 and has an achievement level of D would make them 6 months behind the expected level (of 0.5) and would result in a red conditionally formatted cell. There are 2 semesters, and the semester 1 report will be 0.5 less than the end of year score e.g. grade 3 student at the expected level for semester 1 will show a score of 2.5.

I hope all of that makes sense, and please ask any questions if there's anything I have failed to mention! Thanks heaps!

Which version of Excel are you using? There is no version 11.

Because you put it there when you joined!

Please go to your profile and update it.

You will need to add a helper column and apply a whole number to each item in your scale (you are in good hands - I am a retired teacher and Head of Department, so I have a lot of experience in this sort of thing). Is this OK with you?

Actually, on second look, I don't think you do. Give me a couple of minutes ...

Mmm. Tricky, this. It would be easier if the year level were recorded accurately.

OK - got it, I think.

Add the lookup values to the table:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	D
1	Lookup
2	1
3	2
4	3
5	4
6	4.5
7	5
8	5.5
9	6
10	6.5
11	7
12	7.5
13	8
14	8.5
15	9
16	9.5
17	10
18	10.5
19	11
20	11.5
21	12
22	12.5
23	13
24	13.5
25	14
26	14.5
27	15

Sheet: Scale

Then five rules:

=INDEX($D$2:$D$27,MATCH(N4,$A$2:$A$27,0))-INDEX($D$2:$D$27,MATCH($K4,$A$2:$A$27,0)-IF($M4=1,1,0))<=-1
=INDEX($D$2:$D$27,MATCH(N4,$A$2:$A$27,0))-INDEX($D$2:$D$27,MATCH($K4,$A$2:$A$27,0)-IF($M4=1,1,0))=-0.5
=INDEX($D$2:$D$27,MATCH(N4,$A$2:$A$27,0))-INDEX($D$2:$D$27,MATCH($K4,$A$2:$A$27,0)-IF($M4=1,1,0))=0
=INDEX($D$2:$D$27,MATCH(N4,$A$2:$A$27,0))-INDEX($D$2:$D$27,MATCH($K4,$A$2:$A$27,0)-IF($M4=1,1,0))=0.5
=INDEX($D$2:$D$27,MATCH(N4,$A$2:$A$27,0))-INDEX($D$2:$D$27,MATCH($K4,$A$2:$A$27,0)-IF($M4=1,1,0))>=1

The Applies to ... range needs to cover all columns you need covering.

There may be a slight issue with some of the lower grades - the values in D might need to be tweaked there, but you know your data better than I do!

I've removed all rules you'd created from the Scale worksheet and set the rules up there as a demo (cells N4 to N13). This seems to produce the correct reults. Let me know. Select cell N4 and open the Conditional Formatting > Manage Rules dialog.

Just to add to the above, I don't understand student 3's colour coding (you have given yellow).

The student is, as I understand it, in the first semester of Y1, so the target grade would be the grade for F.5, which is a 1, so how can a score of 0.5 be on target?

That's the only one that doesn't work for me.

If you need the CF formula rule explaining, please just shout.

Ah, well there you go.

Glad to have helped.