Scratch that its worked for one subject but not the other I've just tried. :(

Printable View

- 1st July 2013, 02:35 PMbastonc
Scratch that its worked for one subject but not the other I've just tried. :(

- 1st July 2013, 03:14 PMbastonc
Scratch that, couldn't replicate it with other Subjects. pickled!

- 2nd July 2013, 12:50 PMHughR
I know that you've now cracked it, but I just read this thread while looking for something else and I think I might be able to answer an issue from your original question regarding a 'Blank' value and a 'Zero' value. On one of the Capita Stock Templates that I used recently for End of Key Stage 3 (KS3 C. Subject TA 2013), there was a Validation Formula used for carrying out a data presence check. The first column on the template was called 'Zero Column' which was a 'Formula Review' with no result set. The Formula Type is 'Equals' with a Constant Value of 0 (Column/Constant). So this is basically an Initialiser that can then be used in Grade Tallys and IF Then Else formulas if need be.

I know you've resolved the problem already, I just thought I'd point out this method of setting a default 'Zero' value. - 2nd July 2013, 01:23 PMbastonc
Thanks HughR,

I thought I had cracked it but I couldn't replicate the if statement.

I will have a look at the equals formula you mentioned. - 2nd July 2013, 01:48 PMHughR
OK, can I just ask what exactly you're trying to achieve? I'm guessing that you want SIMS to return a Zero Value when a blank cell is checked, and when an occupied cell is checked, it returns the value?

If that's what you want to do, then you just need to use the Zero Column, followed by a "Grade Tally Column" [you'd need to put in all available grades as the criteria], then a Plus Formula ("Zero Column" + "Grade Tally Column"), then check for 1s and 0s. So:

Zero Column KS2 Score Grade Tally Column Zero Column+Grade Tally Column If Then Else Output 0 3 (tally value of 1) 1 If >0 Then "KS2 Score" Else 0 3 0 BLANK BLANK 0 DITTO 0 0 4 (tally value of 1) 1 DITTO 4

Obviously, this depends on what you're doing but if it's something like this, then this should work. Or a variation thereof.

{EDIT} Sorry, this is really hard to read - I tabulated it, but it didn't come out in the post. Trying again with an HTML Table. - 2nd July 2013, 02:30 PMvikpaw
That's sort of what I suggested earlier. Just sum with zero or plus zero cant you to make it something usable in a formula. Useful also to have a blank or n/a grade in your gradeset with value of 0

- 3rd July 2013, 08:03 AMbastonc
Hi HughR,

Please see the table below. Hope it helps.

Maths KS2 Ma Year 7 Baseline If(Maths KS2=BLANK,Ma Year 7 Baseline,Maths KS2) 5c 4a 5c BLANK 5b 5b 3a 4c 3a

I have gone through the process you have described and in the "Zero Column+Grade Tally Column" column I am getting a list of 1's but no 0's.

Attachment 19141 - 3rd July 2013, 09:41 AMHughR
Hi bastonc,

This is very odd, I have just tested the Marksheet I referred to previously, (temporarily deleted pupil data to test a blank field) and the output for a blank field was a zero.

I’m going to ask some questions, some of which may sound daft, but I think there may be something happening in the background that is preventing a value from being returned. So please excuse me if these seem really obvious:

In the Aspect Details for the KS2 Grades does the Grade Set have a background numerical value? E.g. 2B = 17.00

In your Grade Tally formula, are you using the full available grade set: 1C,1B,1A, 2C,2B,2A, 3C,3B,3A, 4C,4B,4A, 5C,5B,5A,6C,6B,6A,7C,7B,7A,8C,8B,8A

In the formula for “ZERO COLUMN + MA KS2 GRADE” are you using: ‘Data Review using Formula’ and if so, are you using Formula type ‘SUM’ and summing the Zero Column and the Grade Tally Column? This should take the Zero column figure regardless of whether there’s a blank anywhere else. Are you definitely including the Zero Column in the SUM? - 3rd July 2013, 10:08 AMvikpaw
i can confirm @

__HughR__'s method worked for me.

It might be easier to add a blank / 0 to the gradeset then just fill it in where there is a blank. saves a lot of formula cols . then just need to do an if then else... - 3rd July 2013, 11:58 AMbastonc
Hi Guys,

I was using the formula type 'PLUS' for the “ZERO COLUMN + MA KS2 GRADE” by mistake. I have changed this to 'SUM' and it looks like its working.

Thank you for all your help.

P.S. Its a lot of additonal columns for what should be a very simple if statement. - 3rd July 2013, 12:28 PMHughR