MIS Systems Thread, Nested IF statements in Technical; Does anyone have any experience of using nested IF statements with more than 4 comparisons in SIMS?...
1. ## Nested IF statements

Does anyone have any experience of using nested IF statements with more than 4 comparisons in SIMS?

2. Not that I have ever used SIMS but can you not do it with logical operators:
if (((studentStatus=='sick') AND (housecolour=='green')) OR (housecolour=='yellow')) then
?

If not does SIMS support CASE or SWITCH statments which are designed to evaluate lists of conditionals.

3. @SYNACK SIMS - logical? LOL

@philserre - can it do more than 4? i've used the max which was 4 plus a default.
e.g. if less than -2 red else if less than -1 orange else if less than 1 white else if less than 2 blue else green.

4. Thanks for the reply but the SIMS options are very limited. Whereas Excel is limited to 7 statements, SIMS is limited to 4 which means a more convoluted solution will be necessary, if possible at all.

No, it can't do more than four - that's really the problem. I could just about live with seven. What I want to do is put 1 of 7 baseline National Curriculum grades in which then predict an outcome grade for three years later.

6. Ah, i think i saw a similar post on support.

Anything complex is going to be a pain in SIMs. Most people move the data out to Excel, you could then reimport the predictions.
There are other methods used to make predictions, i think using trendlines and such, but we havent done that. Some people use other data like FFT, don't you have access to those, rather than doing your own formula?

What you would have to do, which is a pain, is to split the data at point 4 say and then store it in a new aspect. Then use the 4 + default formula on that if that makes sense. I have done similar for primary school maths tests, where they take two streams higher or lower. So split your baseline into two new temporary columns, run your conditions on both, such that one column gives the prediction on a scale and the other zeroes. Then add them together, and then convert that back to a predicted grade. I hope that makes sense.

7. Yes, the 'similar post' was probably mine?!

I've had ago at what I think you suggested and it works in Excel OK. I've used the same logic in SIMS and I can't even get the first part to work. I enter the baseline grades and click ‘calculate’ and the baseline grades are all returned into the “4 pred grades” column. So I have two columns with the same grades in. If you have the time, and you feel inclined, please check the following for me.

I’m trying to produce a nested IF statement which looks at National Curriculum sub-levels (e.g. 4c, stored in aspect “ICT Baseline”) and converts them into an end of Key Stage predicted levels.

So in the setup window for the formula column (called “4 pred grades”) I have the following:
IF “ICT Baseline” = “3a” THEN “5a” ELSE
IF “ICT Baseline” = “4c” THEN “6c” ELSE
IF “ICT Baseline” = “4b” THEN “6b” ELSE
IF “ICT Baseline” = “4a” THEN “6a” ELSE
ICT Baseline”

I have ignored the colour options.

Because of the limit to 4 comparisons in SIMS, I was then hoping to repeat the NITE process for baseline levels 5c, 5b and 5a in a separate column (called “All pred grades”) to cover the full range.

IF “ICT Baseline” = “5c” THEN “7c” ELSE
IF “ICT Baseline” = “5b” THEN “7b” ELSE
IF “ICT Baseline” = “5a” THEN “7a” ELSE

Thanks, Phil

8. Within SIMS you could use the numeric value of the sub-level (as opposed to the grade) and use a formula to add the numeric value equivalent to two levels of progress to it. Did something similar recently for a secondary school myself.

9. Thanks, I've been thinking about that as a solution but I would want staff to enter a sub-level, which is then translated into the grade set numeric value in a separate (hidden) column, before the final calc takes place. Any idea on how to do that translation without using another round of nested IF's?

10. OK, I've worked it out now.

Instead of IF “ICT Baseline” = “3a” THEN “5a” ELSE, it's necessary to look back to the related SIMS grade set and put a numeric equivalent value into the formula.

3a = 23 points so the formula becomes IF “ICT Baseline” = “23” THEN “5a” ELSE and it works!

Thanks to all for the interest.

11. What i said would be possible, but if you only need straigtforward mappings, then using the points value and gradesets would be easiest.

To make it even easier, create a gradeset with all your sublevels in, if you don't already. At my last school when ofsted was on the door, we went to the point of using extremely fine grades so we had decimals, 3.1, 3.2, ....3.9, 4 it took ages to setup, but so long as the points match the official ones for each actual level, and you split the ones inbetween, it's quite usable.

Not sure if that's what you're doing or not, but assume, 2 levels is 6 pts and your points scale is linear. Then get your formula to add 6pts to the baseline, then do a marks to grade formula which will convert the points back to a grade, using the fine gradeset. You could do the lot in 2 columns with no IFs.

Edit: actually, that's exactly what @iansimpson888 suggested.

12. Just thinking, i also extended that gradeset to show negatives values as well, but had to managed the offset between zero and the first level to make it work - not sure what i did. Can't remember exactly how or why we needed to do that, something to do with calculating differences and lookups.

13. I have used a KS3 gradeset but I was fooled by the way the SIMS formula operates since the process is so straightforward in Excel. I can't understand why NITE's are limited to 4 since most grade sets that school's will use involve more than four levels.

Thanks for your help with this.

SHARE: