I am trying to put together a formula that will calculate the total points required to meet the minimum passing score and have a “Points Needed to Graduate” reflected based on gradebook entries to date.
POSSIBLE 100 POINTS
MINIMUM 80 POINTS TO PASS BASED ON ANY COMBINATION OF THE FOLLOWING:
30 POINTS MAX POSSIBLE FROM PARTICIPATION (CELL L7)
70 POINTS MAX POSSIBLE FROM TWO EXAMS: 35 POINTS MAX TUE EXAM (CELL N7) AND 35 POINTS MAX THU EXAM (CELL O7)
“POINTS NEEDED TO GRADUATE” IS CELL R7.
I currently have the formula “=80-(L7+N7+O7)” in CELL R7 that appears to do the trick; however, if the student scores more than the minimum score of 80 (e.g., 100), the formula returns “-20.” I would like the formula to return “0” or a “-“ for any negative score, i.e., when the score exceeds the minimum.
Additionally, I would like CELL R7 to only calculate the “Points Needed to Graduate” when CELLS L7, N7, and O7 have entries.
I’m not sure if this is what you are looking for, so try it and let us know:
=IF(COUNTA(L7,N7,O7)>0,IF(SUM(L7,N7,O7)<80,80-SUM(L7,N7,O7),""),"")
This will return a blank cell unless there is a value in any one or more of the cells L7,N7 and O7. IF(COUNTA(…)
If there is a value in any of those cells and the SUM is =80, it will return a blank cell. (No more points needed)
The one thing that is not clear to me is whether a student can get more than the MAX points for any one of those criteria.
For example, a 40 in Participation, a 40 on the Tues exam and a 5 on the Thur exam would give them 85 points, but it wouldn’t meet the criteria listed. Are those grades possible or are the MAX values you mention the MAX that will be awarded?
Do you see the difference? Whether or not my formula will work depends on the answer to that question.