Colouring cells in Assessment

[SouthCoastSaint]

What is the best way please to add colour to my cells... where the HOD for English would like 9 different colours? She has given me the marks parameters (eg 16-22 marks is bright orange) but she has also referrred to those particular marks as Grade 3. She doesn't need this conversion showing on the marksheet but could I somehow utilise the grade conversions (even if in a hidden column)? I can't see how I can get 9 colours using NITE, hopefulyl I am missing something obvious!

[Cache]

How is the data being input into the Marksheet - as marks or as grades?

 

Assuming it's going in as marks, do you already have a grade set which would utilise these values or would you need to create a new one?

 

What you could try is building up the NITE by using the less then property starting with your lowest first, so <16 = red, <22 = bright orange, <28 = orange etc and I think it should then stop at the first match for you providing it is in order. You'd need to test it to see if that is the case though, I can't quite remember and it's a while since I worked in assessment properly (but need to start getting my head back in to gear for it for the new term).

 

If it doesn't work then providing you have a gradeset or you create one which ties those values to the grades, then a hidden marks to grade formula based off the mark entered by the teacher and then you can do your NITE based off the hidden formula on the grade.

[SouthCoastSaint]

The HOD wants all aspects in question to be entered as marks out of 40 (which may then later be converted to the new 1-9 grades). I can set up the colours ok, just not enough of them as she needs 9 different colours. I don't think it's possible, I have emailed her asking if she can live with wider colour bands!

[pcstru]

I'd write a small macro subroutine. It would use a tab with a column for bands and a column for the colour. The user can fill these in allowing them to define as many graduations and colours as they want. The Macro could be fed ranges of cells which would be scanned and depending on their value be set to a colour as defined by the table. The calls to the sub could written into (say) the workbook recalculate event.

[Cache]

Aha, I see. I forgot about the limit on NITE. What you could try then is having 2, possibly 3 NITE columns but I've not fully thought it through so it might not work or even be possible. Your first NITE column would do your first 4 < values and then have an Else condition of something like NA and not set a colour. I'd then see if you can do with your second NITE, start by looking to see if your first NITE != NA and leave the then blank, then continue with your NITE for the remainder of your values. You'd then have a 3rd NITE column which the first 2 ITE's would check for NA's before finishing off the remainder of your colouring in.

 

It might end up in a huge mess and it might not work, but it's the only way I can see of trying to achieve it in SIMS at the moment.

 

Edit: I would hide the 3 NITE columns as it will be confusing to anyone looking at it even if it does work and have them colouring the marks column with the approrpiate value or colouring another column in which you set the value to the grade.

Edited July 28, 2015 by Cache

[SouthCoastSaint]

You were thinking along the same lines as me Cache, as long as assessment manager looks at the formula columns in order. Each year has a marksheet with about 8 assessment strands on them - I will avoid using three columns if the HOD is happy to widen the colour bands. If I add a second column converting the marks to a 1-9 grade, this would be easier to analyse than colour anyway, so she might be happy with that solution.

 

Pcstru - I was not clear in my original post - the cells are in a Sims marksheet, not excel or an individual report.

[pcstru]

Pcstru - I was not clear in my original post - the cells are in a Sims marksheet, not excel or an individual report.

Heh, given the forum and thread title, I should have realised. It's my age!

[PhilNeal]

Personally I'd say that having 9 colours will not make it easy to see the important stuff!

[SouthCoastSaint]

Personally I'd say that having 9 colours will not make it easy to see the important stuff!

 

I quite agree.

[pcstru]

Personally I'd say that having 9 colours will not make it easy to see the important stuff!

Depends what the colours are and what's important. I've seen nice 9 colour RAG graduations which give a more refined view of the underlying data and are intuitive enough that people just grasp them. But it is easy to be fiddling about with state representation and lose sight of the effort you put into programming yourself that light purple was and then scratching your head when others don't quite get it.