[sims] Using = blank in an Assessment Template

[bastonc]

Hi,

 

Is it possible to use the following formula in SIMS.net

 

if [Column/Constant] = blank, then use [Column/Constant]?

 

Edited June 28, 2013 by bastonc

[vikpaw]

No i don't think so, it might depend on the column type. I'm sure there are tricks to overcome this, like adding something to all columns like 0 if it's numeric, then testing for 0.

[bastonc]

Hi vikpaw,

 

Thanks for your response.

 

I am now trying;

 

if points column A is >0, then use whats in grade column B otherwise use whats in grade column C

 

I'm still not getting anywhere.

 

Any ideas?

[tombry]

I know when I made my Eng Bacc aspects it was an absolute pain, as it seems that SIMS counts a blank as both >0 and <0 (or 1).

 

what are you trying to get your marksheet to do?

[bastonc]

Hi tombry,

 

I’m trying to have it so that if a pupil has a KS2 level then that is used to calculated their end of year/end of KS target, if they haven't got a KS2 level (many of our Service Pupils don’t) then use their subject baseline level.

 

So,

 

If [KS2] =”” then [baseline] otherwise [KS2]

 

I have also tried,

 

If [KS2 pointscore] <= 0 then [baseline] otherwise [KS2]

 

Hope that makes sense

[tombry]

well, I think you could probably do it like I had to do my EBacc then.

 

set a column to grade tally the KS2 aspect, tallying all grades, then set a column to count if that tally column = 1, make that Y/N

 

then your if else statement is if your Y/N field = Y, use KS2, else Baseline

 

make sense?

[bastonc]

thanks tombry,

 

I'm getting there with it.

 

I now have a column with 1.00's and blanks.

 

I can't find 'count' from the formula type column, am I missing something?

 

Also tried

 

if [tally column] =1.00 use [KS2] else [baseline] but nothing.

[tombry]

sorry, didn't mean "count" - it's a simple If Then statement, if tally column = 1, then Y, else N

 

and I don't know why it works one way and not the other, neither did my LA support team

[bastonc]

Ok, so I now have:

 

a column that puts a 1.00 where there are [KS2 level]

 

a column that puts a Y if there is a 1.00

 

a column thats equals the Y column (because it wouldn't let me if a if column)

 

and the actual calculation column

 

if Y then [KS2], else [baseline]

 

Unfortunately I'm still not getting any baseline appearing for pupils with no KS2

 

I have noticed aswell that there are no N's appeaing in the rows with no 1.00's

[number34]

This probably not much help but...

In similar situations I have given up trying to use a formula as there is just not the functionality in SIMS. I have all the relevant aspects in a template, make up mark sheets as required and then used the filters at the top of the columns to search for blanks. I then filter the other columns and just copy from column to column as required..

[tombry]

Yeah, the Ns don't matter so much.

 

Now I'm stumped.

 

sorry.

[bastonc]

No worries tombry.

 

Thanks for your help.

[tmcd35]

Ok, so I now have:

 

a column that puts a 1.00 where there are [KS2 level]

 

a column that puts a Y if there is a 1.00

 

a column thats equals the Y column (because it wouldn't let me if a if column)

 

and the actual calculation column

 

if Y then [KS2], else [baseline]

 

Unfortunately I'm still not getting any baseline appearing for pupils with no KS2

 

I have noticed aswell that there are no N's appeaing in the rows with no 1.00's

 

An idea...

 

Column 1 = KS2 grade

Column 2 = Baseline grade

Column 3 = If "column 1 < "value for lowest possible score" then "column 2" else "column 1"

 

A "1C" is worth 7 points so IF "Column 1" < 7 THEN "Column 2" else "Column 1"

 

SIMS should see the underlying value of th grades in column1 so there's no need to go through extra steps converting grades to values to make the comparison

[bastonc]

Hi tmcd35,

 

Thanks for your post. I have tried it and still have the same problem.

[bastonc]

CRACKED IT!!

 

Changed the if statement column from Data Review with Formula to Data Entry with formula

 

Then went through the original process;

 

if [KS2] = "" then [baseline], else [KS2]

 

[bastonc]

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

[bastonc]

Scratch that, couldn't replicate it with other Subjects. pickled!

[HughR]

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.

[bastonc]

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.

[HughR]

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:

 

[table=width: 750, class: grid, align: center]

[tr]

[td]Zero Column[/td]

[td]KS2 Score[/td]

[td]Grade Tally Column[/td]

[td]Zero Column+Grade Tally Column[/td]

[td]If Then Else[/td]

[td]Output[/td]

[/tr]

[tr]

[td]0[/td]

[td]3[/td]

[td](tally value of 1)[/td]

[td]1[/td]

[td]If >0 Then "KS2 Score" Else 0[/td]

[td]3[/td]

[/tr]

[tr]

[td]0[/td]

[td]BLANK[/td]

[td]BLANK[/td]

[td]0[/td]

[td]DITTO[/td]

[td]0[/td]

[/tr]

[tr]

[td]0[/td]

[td]4[/td]

[td](tally value of 1)[/td]

[td]1[/td]

[td]DITTO[/td]

[td]4[/td]

[/tr]

[/table]

 

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.

Edited July 2, 2013 by HughR

[vikpaw]

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

[bastonc]

Hi HughR,

 

Please see the table below. Hope it helps.

 

[table=width: 500, class: grid]

[tr]

[td]Maths KS2[/td]

[td]Ma Year 7 Baseline[/td]

[td]If(Maths KS2=BLANK,Ma Year 7 Baseline,Maths KS2)[/td]

[/tr]

[tr]

[td]5c[/td]

[td]4a[/td]

[td]5c[/td]

[/tr]

[tr]

[td]BLANK[/td]

[td]5b[/td]

[td]5b[/td]

[/tr]

[tr]

[td]3a[/td]

[td] 4c[/td]

[td]3a[/td]

[/tr]

[/table]

 

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.

 

Edited July 3, 2013 by bastonc

[HughR]

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?

Edited July 3, 2013 by HughR

[vikpaw]

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...

[bastonc]

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.