Grade Point Average Calculation

[jmille011]

I'm trying to work on a formula that will do the math for this grading system that I'm working on but using Case and IF keep send back the first TRUE. There are couple different parameters that the "Semester Average" are looking for. 

First the solution looks for the First Semester Exam(SubE1) to see if it's empty or not, then if it is, it should pull Quarter 1(Sub1Q1) add Quarter 2(Sub1Q2) divide by 2 to get the solution. If Quarter 2(SubQ2) is missing and so is First Semester Exam(Sub1E1) then it should just fill in with Quarter 1's grade. The last part is for weighting of final exams per grade level, but it just doesn't seem to work. Help needed.

 

 

Case ( Sub1E1 = "" ; (Sub1Q1 + Sub1Q2) / 2 ; Sub1Q2  Sub1E1 = "" ; Sub1Q1 ; Grade_Level ≥ 11 ; (Sub1Q1 * .4) + (Sub1Q2 * .4) + (Sub1E1 * .2) ; Grade_Level < 11 ; (Sub1Q1 * .45) + (Sub1Q2 * .45) + (Sub1E1 * .1))

[comment]

This is very difficult to read. And confusing: you have First Semester Exam(SubE1) and then First Semester Exam(Sub1E1). Are they the same? Also, "doesn't seem to work" is not a useful description of the problem.

If I had to guess, I would say this: if you want to have one result when X is empty, and another result when both X and Y are empty, then you must test for both X and Y being empty first. Otherwise the function will never get to it, because it exits when it encounters the first test that evaluates as true.

IOW, your formula should be structured like this:

Case ( 
IsEmpty ( X & Y ) ;
// some result here ;

IsEmpty ( X ) ;
// another result here ;

//default result (or more test/result pairs) here
)

 

[Wim Decorte]

Try to format your formula so that it is easier to read and follow:

 

Case(
    Sub1E1 = "";
    ( Sub1Q1 + Sub1Q2 ) / 2;
    Sub1Q2 Sub1E1 = "";
    Sub1Q1;
    Grade_Level ≥ 11;
    ( Sub1Q1 * .4 ) + ( Sub1Q2 * .4 ) + ( Sub1E1 * .2 );
    Grade_Level < 11;
    ( Sub1Q1 * .45 ) + ( Sub1Q2 * .45 ) + ( Sub1E1 * .1 )
)

 

The 2nd test in there: is "sub1q2 sub1e1' one field name?

[jmille011]

That cleared it up by putting the two variables in the first testing. Thanks 

[jmille011]

If(IsEmpty ( Sub9First_Semester or Sub9Second_Semester); Sub9Grade_Point_Average/2 ; Case ( Sub9Final_Grade ≥ "96.5" ; "4" ; Sub9Final_Grade  ≥  "92.5" ; "4" ; Sub9Final_Grade  ≥  "89.5"; "3.7" ; Sub9Final_Grade  ≥ 86.5; "3.3";  Sub9Final_Grade ≥ 82.5 ; "3" ; Sub9Final_Grade ≥ 79.5 ; "2.7" ; Sub9Final_Grade ≥ 76.5 ; "2.3" ; Sub9Final_Grade ≥ 72.5 ; "2" ; Sub9Final_Grade ≥ 69.5 ; "1.7" ; Sub9Final_Grade  ≥ 66.5 ; "1.3" ; Sub9Final_Grade  ≥ 64.9 ; "1" ; Sub9Final_Grade < 64 ; "0" ; ""))

 

I'm trying to make my Grade Point Average field divide by 2 if one or the other Semester is empty, but somewhere in this calculation, it's not working. Is something in the wrong order?

Edited May 23, 2017 by jmille011
add pic

[siroos12]

If(IsEmpty ( Sub9First_Semester ) or IsEmpty ( Sub9Second_Semester); .....

[Wim Decorte]

to expand on that; you have this

 

isEmpty ( Sub9First_Semester or Sub9Second_Semester)

 

this part:

Sub9First_Semester or Sub9Second_Semester

will result in a boolean result; 0 or 1, so it will never be empty

 

[comment]

In addition, there is really no good reason to nest Case() within If(); you can do it all in a single call of the Case() function:

Case ( 
IsEmpty ( Sub9First_Semester ) or IsEmpty ( Sub9Second_Semester ) ; Sub9Grade_Point_Average / 2 ; 
Sub9Final_Grade ≥ "96.5" ; "4" ; 
Sub9Final_Grade ≥ "92.5" ; "4" ; 
Sub9Final_Grade ≥ "89.5"; "3.7" ; 
Sub9Final_Grade ≥ 86.5; "3.3"; 
Sub9Final_Grade ≥ 82.5 ; "3" ; 
Sub9Final_Grade ≥ 79.5 ; "2.7" ; 
Sub9Final_Grade ≥ 76.5 ; "2.3" ; 
Sub9Final_Grade ≥ 72.5 ; "2" ; 
Sub9Final_Grade ≥ 69.5 ; "1.7" ; 
Sub9Final_Grade ≥ 66.5 ; "1.3" ; 
Sub9Final_Grade ≥ 64.9 ; "1" ; 
Sub9Final_Grade < 64 ; "0" 
)

Note also that specifying an empty default result is redundant. 

 

Edited May 23, 2017 by comment

[jmille011]

Case ( IsEmpty ( Sub1E1 & Sub1Q2 ); Sub1Q1 ; IsEmpty ( Sub1E1 ) ; (Sub1Q2 + Sub1Q1) / 2 ; Grade_Level ≥ 11 ; (Sub1Q1 * .4) + (Sub1Q2 * .4) + (Sub1E1 * .2) ; Grade_Level < 11 ; (Sub1Q1 * .45) + (Sub1Q2 * .45) + (Sub1E1 * .1))

Back to the previous formula, I'm still having issues with the formula performing correctly when it comes to the Grade Level aspect. Should I reorder the way the Grade Level section goes through the operation? 

Grades 7-9 are operating upon the first Grade Level check >=11 Grade. 10th Grade is the only Grade that performs correctly when doing the math.

[comment]

1 hour ago, jmille011 said:

Grades 7-9 are operating upon the first Grade Level check >=11 Grade. 10th Grade is the only Grade that performs correctly when doing the math.

That would be the expected behavior if Grade_Level is defined as a Text field.

--
P.S. Please do us the favor of formatting your calculations and placing them inside a code block when posting here.

[jmille011]

The Grade_Level is set up as a number field, not a text field.

Nevermind, got it to work. Had to play around with the formula a little.

[comment]

1 hour ago, jmille011 said:

Had to play around with the formula a little.

You speak in riddles.