[sims] Colour Coding Cells in ASM Marksheets

[tben2505]

Hi folks,

 

I have a question. Please consider this example:

 

At the end of term we have grades for each pupil in L6 for all the subjects they study.

We also have a prediction (Alis).

We derive value added scores based on these two points. So If in art I got a B (50 points) and my Alis prediction was 56 (A/B) my VA is -6.

 

Before SIMS I'd use conditional formatting to change the colour of the L6 grade in Excel, depending on the VA (+/- half a grade VA, made the grade go greed/red)

 

I'm aware this can be done in SIMS - but I'm finding myself setting a marksheet to calculate the VA, which obviously requires the Grade and Alis aspects in the marksheet.

Then when I want to implement the nested if then else, I have to add a column for data review using formula, which as you know doesn't include an aspect.

This column returns the grade in the column, but it's

a) read only so can't be edited

b) even if it could be edited it's not the actual grade

c) most importantly, it only brings through the grade if either of the nested ifs are met, ie if the VA is +/-5, anyone with a VA of 0 for example, or blank because we have no Alis for them, returns nothing in this column.

 

I'd like to know what others do for this numerical VA, ((rather than comparing to a target grade which is a lot easier); and if I'm missing something really obvious!

 

Cheers all

 

T

[RootsSchImp]

There's an option 'also colour column' at the bottom of heated if then else. So you can keep the colour coding column hidden and just show the coloured grades column.

[tben2505]

I am a dufus.

 

Thank you!!

[vikpaw]

You can also do data entry by formula and store a result in an aspect. Do the calculation and then store it, or even convert back to a grade difference depending on your parameters.

[tben2505]

Hi Vikpaw - not sure I follow that one, sorry!?

[bobsmith]

Example:

1. New column for data review using formula, formula type = "difference", Alis - Grade - call this column "diff"

2. New column for data review using formula: formula type = "nested if then else", column "diff" > constant 5, column diff < -5, else "some other colour" - colour column "grade"

Hide both of these columns.

The original grade is now coloured using the conditional formatting.

 

If you don't have an ALIS for a certain student you could enter a "0" and have a 3rd condition that operates first and checks for this 0 in the ALIS column.

[tben2505]

Yeah cheers Bob - done this was just colouring the actual grade cell I couldn't do - as I missed the option at the bottom of the page!

 

If there isn't an Alis - then the rules of IF +/-5 won't be applied, therefore no colour - which is what we'd want...

 

It's a PAIN to set up - but I'm hoping I can clone for the upper 6th!

 

Cheers all!

 

T

[vikpaw]

Hi Vikpaw - not sure I follow that one, sorry!?

You mentioned having to use Date Review using formula and it not having an aspect.

You can create columns of type Date Entry using formula, you then link it to one of your existing aspects and then the result of the formula will be stored. You might not need it in this case, but it's useful to know.

 

One thing you might want to do is convert the difference in points back into a grade and store this. So a VA of -12 points could then be used to do a points to grade conversion and you can have it lookup that -12 is -2 grades (or whatever it is). It's fairly trivial to see by inspection but for bulk data you might want to see a column of 1 grade below, 2 grades below etc so it's more meaningful. Or, for whatever reason, so you can target intervention on your major underperformers , or perhaps the borderliners.

 

I remember doing fine grades and splitting a normal grade down into decimals, so you could get a national curriculum level of 3.1,3.2,3.3, etc. then had a massive gradeset that showed all of these fine grades with points both postive and negative. Again it's a huge job to setup, but once it's there, you can keep re-using it.