Colour coding data in mark sheets on entry using nested if then formula

[carotheoptimist]

Hi

The school I am working for has mastery based grade sets it wants to use in marksheets. i have set up the grade sets that are mastery skills based e.g. Developing, secure, exceeding etc and attached a nominal value to each 'grade'. However, when I set up a nested if then formula using 'data for review using formula' then enter some data it doesn't change colour.

 

I set a value of 3 for 'poor' in the grade set and use that as the constant/value in the nested formula so if 'y7engprog' is the aspect and the value in the cell is 3 then it turns the cell red. I've tried it using the word 'poor' as the constant/value, that doesn't work either.

 

Any advice would be greatly appreciated,

Caro

[mrwoberts]

I've done something similar to that in the past, but I used conditional formatting.

It'll be on an old backup somewhere if you were desparate, but without seeing a sample of the spreadsheet it's hard to picture what you mean.

[carotheoptimist]

Thanks for replying, yes I have used conditional formatting when the marksheets has been exported but I believe it is possible to do by setting up a nested if then formula within the template. My aspect is 'y7engprog', the grade set is 'progress' which has 3 values - below, good, outstanding. I have assigned nominal values of 3, 7 and 9 and one grade of 'B' to below. Do I have to assign grades to all of the grade set ie the first letter of the word so that the 'if' part of the formula can = that letter then the cell containing the aspect can change colour. So if the constant/value = B then y7engprog cell turns red on entry and on save? Sorry if this isn't clear!

[mrwoberts]

Any chance of a useful sample of part of this spreadsheet?

Would be much easier to troubleshoot.

[carotheoptimist]

Hi

Yes I shall capture a screen print of said grade set and nested if then formula if possible unless I can work it out tomorrow,

thanks

[SouthCoastSaint]

If I understand you correctly, this should work. Call your formula column Colour or something generic, and make it hidden. At the bottom of your NITE formula, make sure you complete the 'also colour column' and put your mastery one in there. Apologies if I have misunderstood what you are trying to do.

[RootsSchImp]

Nested If Then Else uses grade values so what you want is IF y7engprog = 3 THEN (in this box you need a value, not just a colour, so let's say P colour red) ELSE IF y7engprog = 5 etc

 

At the bottom of the formula box there's an 'also colour this column' selector. If you want y7engprog to be colour coded add it here.

[carotheoptimist]

Thanks everyone this is now working fine. Does anyone know if a template is cloned does the nested if then formulae automatically work? My aspects are not in years but are subject e.g. Engprog, the result sets are by year and half term. If I set up a template for y8 English and then clone it to year 9 will my nested if then formulae work as they are not year group based?