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

 

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.

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"

)

)

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?

 

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.

THANK YOU SO MUCH! Worked like a charm!

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

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.

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