complicated calculation

[TMC09]

We have exemptions for students based on various criteria. I need calculations to determine if the student is exempt or not depending on the various criteria. It gets kind of complicated. I'm not very good a figuring out complex calculations. Here is the criteria:

average 95 or above, maximum of 3 absences, maximum of 3 tardies

average 90-94, maximum of 2 absences, maximum of 2 tardies

average 70-89, maximum of 1 absence, maximum of 1 tardy

any violation of conduct is an automatic "not exempt" (I have fields for ISS, U, and Academic Integrity)

Here is what I tried to do for 95 and above:

Case (
P1_Rounded ≥ 95 and P1_Absence ≥ 3 and P1_Tardy ≥ 3 and Conduct="U" or ISS="ISS" or academic_integrity="AI"; "Not Exempt" ;
P1_Rounded ≥ 95 and P1_Absence ≤ 3 and P1_Tardy ≤ 3 ; "Exempt";"" )

 

Here is what I tried to do for 90-94:

Case (
P1_Rounded ≥ 90 and P1_Rounded ≤ 94 and P1_Absence ≤ 2 and P1_Tardy ≤ 2 and Conduct="U" or ISS="ISS" or academic_integrity="AI"; "Not Exempt" ;
P1_Rounded  ≥ 90 and P1_Rounded ≤ 94 and P1_Absence ≤ 2 and P1_Tardy ≤ 2 ; "Exempt";"" )

and then for the 70-89:

Case (
P1_Rounded ≥ 70 and P1_Rounded ≤ 89 and P1_Absence ≤1 and P1_Tardy ≤ 1 and Conduct="U" or ISS="ISS" or academic_integrity="AI"; "Not Exempt" ;
P1_Rounded  ≥ 70 and P1_Rounded ≤ 89 and P1_Absence ≤ 1 and P1_Tardy ≤ 1 ; "Exempt";"" )

Nothing is working right. What am I doing wrong?

Any help would be appreciated. Sorry it's so complicated(at least it is for me "newbie")

[Steve Martino]

So to make it a little clearer for 90-94, the student must have:

-Average grade between 90 and 94

-Must have less than or equal to 3 Absence AND 3 Tardy 

-AND-

Must have Conduct=U OR ISS="ISS" OR academic_integrity="AI"

Am I on the right track?

Seems like Conduct, ISS or academic_integrity are all must haves for Exempt, regardless of grade...correct?

Let me know

[Steve Martino]

Try this, again, can't test:

Let([
     @gr=P1_Rounded;

     @param= Case(Conduct="U" or ISS="ISS" or
                  academic_integrity="AI";"E";"N"); 
//----------If they meet this criteria, return "E"--------------

     @test= Case(@gr ≥ 95 and P1_Absence ≤ 3 and P1_Tardy ≤ 3;"E";"N";
                  @gr ≥ 90 and P1_Absence ≤ 2 and P1_Tardy ≤ 2;"E";"N";
                  @gr ≥ 70 and P1_Absence ≤ 1 and P1_Tardy ≤
                   1;"E";"N");
//----------If they meet this criteria, return "E"--------------

     @exempt=Case(@param="E" and @test="E";"Exempt"; "Non-Exempt")
//----------If they meet both critera, return "Exempt"--------------

    ];

@exempt

)

Edited July 16, 2018 by Steve Martino
typo

[Lee Smith]

Hi Steve,

That's the way I read it too.

I made a demo using your calculation, I think there are some ≤ and or ≥ are reversed.

 

complicated caldulation.fmp12

[TMC09]

If they have ISS, U or AI... then they don't get exempt. That is a conduct violation and they get their exemption privileges taken away. So it's an automatic "Not Exempt".

For the 90-94 they can't have more than 2 absences and more than 2 tardies. If they have 3 or more, then they are "not exempt". Less than or equal to 2.

Does that help?

 

Edited July 17, 2018 by TMC09

[Steve Martino]

Ah...so maybe:

Let([
     @gr=P1_Rounded;

     @param= Case(Conduct="U" or ISS="ISS" or
                  academic_integrity="AI";"N";"E"); 
//----------If they meet this criteria, return "N"--------------

     @test= Case(@gr ≥ 95 and P1_Absence ≤ 3 and P1_Tardy ≤ 3;"E";"N";
                  @gr ≥ 90 and P1_Absence ≤ 2 and P1_Tardy ≤ 2;"E";"N";
                  @gr ≥ 70 and P1_Absence ≤ 1 and P1_Tardy ≤
                   1;"E";"N");
//----------If they meet this criteria, return "E"--------------

     @exempt=Case(@param="E" and @test="E";"Exempt"; "Non-Exempt")
//----------If they meet both critera, return "Exempt"--------------

    ];

@exempt

)

[TMC09]

I added a bunch of records to the database that you had attached and added my status (that we did all by hand!) It looks like the calculation you had given me is not looking at the zeros and it's making it "not Exempt" when it should be exempt.

I have attached the DB.

I made the correction that you gave me. But there are a few that have issues.

I put a YES in the Issues field.

We are closer though... thank you so much!!! You have no idea how much your help on this means to me.

 

complicated caldulation Copy.fmp12

Edited July 17, 2018 by TMC09

[Steve Martino]

Sample files help...

Should be right, (bone-head mistake on the calc by me)

complicated calculationSMmod.fmp12

[TMC09]

Oh My Goodness!!! I am sooooo happy!!! I want to cry! Thank you so so so much! I am very grateful for all of the help you have given me. Yall are AMAZING!! Yall have saved me!!

[Steve Martino]

Was much easier after Lee created the file, and you provided some data-saves everyone time.

I received tons of help on these forums, always trying to return the favors.

[Lee Smith]

You are not trying anymore, you are doing a great job.

[Steve Martino]

18 minutes ago, Lee Smith said:

You are not trying anymore, you are doing a great job.

High praise indeed...Thanks Lee

[TMC09]

Well both of you are blessed with knowledge. I am just glad that I could benefit from that. Thank you again!

[Lee Smith]

You are welcome.