Nested if statements

[junito2001]

Hello, I work for a school and I'm creating an evaluation database for our principals to use. Within that we have proficiency areas in which teachers get evaluated and receive a score of HE, E, NI, or U. I have one domain (that holds the proficiency scores) which a can receive 4 possible combinations (PAR1, PAR2, PAR3, PAR4) of scores (ex.. he, he, he, he or he, ni, e, e, etc....); based on the combination, the teacher will then receive a final score for the entire domain of HE, E, NI or U. So example:

 

Teacher1 gets:

PAR1 = HE

PAR2 = E

PAR3 = E

PAR4 = E

 

The teacher's domain final score will be an E. 

 

What is the easiest way I can script these domainX scores without writing out each possible combination out in If statements (which is what I started to do but my eyes are beginning to hurt). Here are the criteria:

 

HE if

• All four PARs are HE
• Three PARS are HE and one E

E if

• Two PARs are E and two are HE
• All four PARs are E
• Three PARs are E and one is either NI or HE

NI if

• Two PARs are E and two are NI
• Any three PARs are NI
• One PAR is NI, one PAR is U and two PARS are E or HE

U if

• Two or more PARs are U

 

Any ideas anyone? Please help!! 

 

[comment]

Nested ifs are for the birds; use the Case() function instead. Note that the order of tests is important.

 

FilterValues() and ValueCount() should also be fairly useful here.

[doughemi]

Create an unstored (per Wim's comment below) calculation field FinalScore.  The calculation should be

 

Let([

~PARlist = List(PAR1;PAR2;PAR3;PAR4);

~list = ¶ & ~PARlist;

~HEtot= PatternCount(~list; "¶HE");

~Etot= PatternCount(~list; "¶E");

~NItot= PatternCount(~list; "¶NI");

~Utot= PatternCount(~list; "¶U")

];

Case(

ValueCount(~PARlist) < 4; "";

 

~Utot >1; "U";

 

~HEtot = 4; "HE";

~HEtot = 3 and ~Etot = 1; "HE";

 

~Etot  = 4; "E";

~Etot = 3 and ~HEtot = 1; "E";

~Etot = 3 and ~NItot = 1; "E";

~Etot = 2 and ~HEtot = 2; "E";

 

"NI"

)

)

[eos]

As comment said: don't use nested If()s, use Case() instead. Actually, you can always use Case(), though it seems that If() has a more natural ring to it.

 

Using ValueCount instead of PatternCount …

Let ( [
    theList = List ( "HE" ; "E" ; "E" ; "E" ) ; // sample data ; replace with List ( PAR1 ; PAR2 ; PAR3 ; PAR4 )
    he = ValueCount ( FilterValues ( theList ; “HE” ) ) ;
    e = ValueCount ( FilterValues ( theList ; “E” ) ) ;
    ni = ValueCount ( FilterValues ( theList ; “NI” ) ) ;
    u = ValueCount ( FilterValues ( theList ; “U” ) )
   ] ;
    Case (
        he + e + ni + u < 4 ; "Incomplete or wrong data!" ;
        he = 4 or ( he = 3 and e = 1 ) ; "HE" ;
        e = 4 or ( he = 2 and e = 2 ) or ( e = 3 and ( he = 1 or ni = 1 ) ) ; "E" ;
        ( ni = 1 and u = 1 and ( e = 2 or he = 2 ) ) or ni = 3 or ( e = 2 and ni = 2 ) ; “NI” ;
        u ≥ 2 ; "U"
  ) )

btw, what about  "HE", "HE", "HE", "U"? Not possible?

[Wim Decorte]

  On 12/2/2013 at 3:44 PM, doughemi said:

 

Create an unstored calculation field FinalScore. 

 

I would probably not go with an unstored calc.  Seems to me that once a score is calculated it should be stored, not recalculated on the fly every time it is displayed.

[junito2001]

THANK YOU SO MUCH! Worked like a charm!

[junito2001]

  On 12/2/2013 at 3:52 PM, eos said:

As comment said: don't use nested If()s, use Case() instead. Actually, you can always use Case(), though it seems that If() has a more natural ring to it.

 

Using ValueCount instead of PatternCount …

Let ( [
    theList = List ( "HE" ; "E" ; "E" ; "E" ) ; // sample data ; replace with List ( PAR1 ; PAR2 ; PAR3 ; PAR4 )
    he = ValueCount ( FilterValues ( theList ; “HE” ) ) ;
    e = ValueCount ( FilterValues ( theList ; “E” ) ) ;
    ni = ValueCount ( FilterValues ( theList ; “NI” ) ) ;
    u = ValueCount ( FilterValues ( theList ; “U” ) )
   ] ;
    Case (
        he + e + ni + u < 4 ; "Incomplete or wrong data!" ;
        he = 4 or ( he = 3 and e = 1 ) ; "HE" ;
        e = 4 or ( he = 2 and e = 2 ) or ( e = 3 and ( he = 1 or ni = 1 ) ) ; "E" ;
        ( ni = 1 and u = 1 and ( e = 2 or he = 2 ) ) or ni = 3 or ( e = 2 and ni = 2 ) ; “NI” ;
        u ≥ 2 ; "U"
  ) )

btw, what about  "HE", "HE", "HE", "U"? Not possible?

 

I brought this up to my administration and they mentioned that anything that does not apply to rules above, will receive an NI. Could that be possible using a Case()? I know Case some what like IF statements but how do I get else (everything else) NI.?

[doughemi]

See my previous post.  The last line in the Case statement does not have a condition.  If each of the previous conditions fail, the Case returns the final statement, which in this instance is "NI". Eos' calculation is more compact, but I like to list each named set of conditions and then have a default for when all named conditions fail.

 

EDIT:  If the administrator's statement means that if one or more of the grades is missing, then the final grade is NI, then remove the line 

 

ValueCount(~PARlist) < 4; "";

 

from the calc.

[junito2001]

That is exactly what I ended up doing thank you again for all your help. Complete success!