Hate Formulas

[Susan01]

Can someone tell me - in a marksheet, can I use nested if in 'Add a column for Data Review' then use the result in another nested if in a second 'add a column for data review'? I've tried setting this up, but when I try to create the second one, it's not showing the first as an available column for the calculation.

 

If only there was an option for IF x AND y then ... life would be a lot simpler and I wouldn't be banging my head off the wall right now!

[LosOjos]

You can't nest '[NESTED] IF' columns unfortunately

 

What are you trying to achieve? There are lots of us here who have taken on SIMS from all sorts of angles, we might be able to think of another approach that will work

[Bromcom_John]

Well from a system agnostic viewpoint why not try using some prime number mathematics.

It does involve adding a couple of columns to convert your grade values into unique integers and back again but it should work.

Take your first set of values and use whatever tools you have available (probably a lookup if your using SIMS, a simple nested if in our own MIS) to convert the unique values from your gradeset into prime numbers.

Do the same with your second set of values but with different prime values.

Multiply these values together in a third column and you get a unique integer for each combination of Column A and B.

Then use "these" values in your final nested if then else.

Because each integer value is unique then they can each be evaluated as what is essentially an If A = X and B = Y Then.

 

Working example,

first grades in column A = A, B, C, D, E

Second Grades in column B = 1,2,3,4,5

If we assign the prime values of 1,3,5,7,11,13,17,19, 23 and 29 to the above grades (in sequence)

We will get the following potential values

 

[table=width: 500, align: center]

[tr]

[td]Column A[/td]

[td]Value[/td]

[td]Column B[/td]

[td]Value[/td]

[td]Product[/td]

[/tr]

[tr]

[td]A[/td]

[td]1[/td]

[td]1[/td]

[td]13[/td]

[td]13[/td]

[/tr]

[tr]

[td]A[/td]

[td]1[/td]

[td]2[/td]

[td]17[/td]

[td]17[/td]

[/tr]

[tr]

[td]A[/td]

[td]1[/td]

[td]3[/td]

[td]19[/td]

[td]19[/td]

[/tr]

[tr]

[td]A[/td]

[td]1[/td]

[td]4[/td]

[td]23[/td]

[td]23[/td]

[/tr]

[tr]

[td]A[/td]

[td]1[/td]

[td]5[/td]

[td]29[/td]

[td]29[/td]

[/tr]

[tr]

[td]B[/td]

[td]3[/td]

[td]1[/td]

[td]13[/td]

[td]39[/td]

[/tr]

[tr]

[td]B[/td]

[td]3[/td]

[td]2[/td]

[td]17[/td]

[td]51[/td]

[/tr]

[tr]

[td]B[/td]

[td]3[/td]

[td]3[/td]

[td]19[/td]

[td]57[/td]

[/tr]

[tr]

[td]B[/td]

[td]3[/td]

[td]4[/td]

[td]23[/td]

[td]69[/td]

[/tr]

[tr]

[td]B[/td]

[td]3[/td]

[td]5[/td]

[td]29[/td]

[td]87[/td]

[/tr]

[tr]

[td]C[/td]

[td]5[/td]

[td]1[/td]

[td]13[/td]

[td]65[/td]

[/tr]

[tr]

[td]C[/td]

[td]5[/td]

[td]2[/td]

[td]17[/td]

[td]85[/td]

[/tr]

[tr]

[td]C[/td]

[td]5[/td]

[td]3[/td]

[td]19[/td]

[td]95[/td]

[/tr]

[tr]

[td]C[/td]

[td]5[/td]

[td]4[/td]

[td]23[/td]

[td]115[/td]

[/tr]

[tr]

[td]C[/td]

[td]5[/td]

[td]5[/td]

[td]29[/td]

[td]145[/td]

[/tr]

[tr]

[td]D[/td]

[td]7[/td]

[td]1[/td]

[td]13[/td]

[td]91[/td]

[/tr]

[tr]

[td]D[/td]

[td]7[/td]

[td]2[/td]

[td]17[/td]

[td]119[/td]

[/tr]

[tr]

[td]D[/td]

[td]7[/td]

[td]3[/td]

[td]19[/td]

[td]133[/td]

[/tr]

[tr]

[td]D[/td]

[td]7[/td]

[td]4[/td]

[td]23[/td]

[td]161[/td]

[/tr]

[tr]

[td]D[/td]

[td]7[/td]

[td]5[/td]

[td]29[/td]

[td]203[/td]

[/tr]

[tr]

[td]E[/td]

[td]11[/td]

[td]1[/td]

[td]13[/td]

[td]143[/td]

[/tr]

[tr]

[td]E[/td]

[td]11[/td]

[td]2[/td]

[td]17[/td]

[td]187[/td]

[/tr]

[tr]

[td]E[/td]

[td]11[/td]

[td]3[/td]

[td]19[/td]

[td]209[/td]

[/tr]

[tr]

[td]E[/td]

[td]11[/td]

[td]4[/td]

[td]23[/td]

[td]253[/td]

[/tr]

[tr]

[td]E[/td]

[td]11[/td]

[td]5[/td]

[td]29[/td]

[td]319[/td]

[/tr]

[/table]

 

So rather than evaluating "IF ColA = A and ColB=5" just evaluate "IF Product = 29 then ...." this way you can have potentially unique evaluation of all 25 possible combinations.

 

Its neither elegant nor simple, but it should work.

Edited November 5, 2015 by Bromcom_John

[Susan01]

Thank you very much!

I’m trying to create a RAG marksheet that takes the resultsof a set of tasks/KPI’s/whatever they end up being called, and calculates anoverall value for Mastery, Secure, Developing or Emerging as follows:

IF >=90% green AND any mastery task green THEN M

ELSE IF >=60% green THEN S

ELSE IF >=30% green or amber THEN D

ELSE E

 

Anyway, I started typing out what I've been trying to do, and in the process realised how I could omit the nested, nested-ifs by changing things around a bit - and ... success at last!

 

Thanks John btw for your solution. It's more complex than I needed (I used two IF X then 1 else 0 statements, then multiplied them together), but it will be stored away for future use.

[pcstru]

Its neither elegant nor simple, but it should work.

That is a very neat approach to fully enumaerate a state system into a list of unique integers (although it should be pointed out that 1 is not prime but 2 is), but it seems unnecessary when concatenating the columns will give a full state system which could then be used in an IF/Case.

[Susan01]

Damn.. just hit the column limit :-(

[vikpaw]

Maybe instead of data review columns it would have worked with data entry columns. Just hide the interim results.

Glad it's working now.

[Bromcom_John]

That is a very neat approach to fully enumaerate a state system into a list of unique integers (although it should be pointed out that 1 is not prime but 2 is), but it seems unnecessary when concatenating the columns will give a full state system which could then be used in an IF/Case.

You're absolutely correct. Usually if I use something like this I will evaluate the lowest value within a range rather than each one individually. The above simply represents the highest granular detail that such a system could implement.

 

Also, while concatenation (is that even a word ) would work in Excel, not sure if the feature is available in SIMS marksheets. it may well be, that'd just be my limited knowledge of Sims Assessment showing.

Edited November 6, 2015 by Bromcom_John