Hello, I've put a lot of work into a grade tracker I'm really proud of but there is one thing it is missing before it's perfect in my view and I was hoping you wonderful people could help. I've tried several searches but I'm struggling to interpret what is being said. I'm not exactly new to excel, it's a skill I'm developing but I'm hoping that if I could learn how to do this, I could then apply it to multiple scenarios as the grade range remains the same for all of our students and all of our year groups.
I've added an example document, which is a mini version of what we're working with (as the actual version is up to column DV with each unit of testing). In the example data case I'd want to compare the grade in column I with the target in column B. Students above target would have their cells coloured green, on target would be amber and below target would be red. I know that some sort of conditional formatting would be neccessary, just not sure what to do here?
The grade range is U, 1, 2, 3, 4, 5, 6, 7, 8, 9. (If the U causes complicates things we could replace it with 0).
Students targets are from 1-9.
PS: In the sample data, grades start with U, then jump to 2 as this is the lowest graded score on this particular paper - but this isn't always the case. Hope this makes sense.
Any help would be much appreciated.
EDIT: It won't let me add an attachment for the sample data
EDIT 2: I've added a clipping of the screen clicking on the grade cell to show how this has been formed.
Capture.PNG
Bookmarks