Solved Excel Formula To Calculate Passing Grade

May 28, 2013 at 12:05:44
Specs: Windows 7
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.


See More: Excel Formula To Calculate Passing Grade

Report •

#1
May 28, 2013 at 17:38:41
When posting in a Help forum such as this, please try to use a Subject Line that tells us something about the contents of your post. If everyone used a generic Subject Line such as "Help With Excel Formula" we wouldn't be able to tell one question from another. The archives would basically be useless.

I have edited the Subject Line of your post to make it more relevant to your question.

Thanks.

DerbyDad03
Office Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
May 28, 2013 at 18:06:02
✔ Best Answer
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 "80 minus that SUM". IF(SUM(...)

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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
May 29, 2013 at 06:41:55
Hi,

Those are the MAX values that can be awarded:

30 pts for participation
35 pts for exam 1
35 pts for exam 2

It appears that the formula you used uses this assumption, i.e., it looks like it is calculating correctly.

Thanks for sharing your time and wisdom.


Report •

Related Solutions

#4
May 29, 2013 at 06:44:38
Understood. Thanks for letting me know.

Report •

Ask Question