Mark Scheme from multiple variables

Hi,

I'm working on a planning exercise where I need to determine the number of students who will need to resit a course of study. The school has a proper system to calculate this for each student individually, however I need to do something a bit more forward thinking.

There are 4 courses in a year worth 30 credits each (for a total of 120). Some departments may have courses at 15 credits each. So for example there may be 3x30 credit courses and 2x15 credit courses for a total of 5 courses. There are only ever 30 or 15 credit courses.

The base rule is that 120 credits are needed in each year to progress to the next.

The pass mark for a course is 40 marks. If you get 0-39, you fail, and you do not obtain the credits. You need to resit that course. Here's where it get a little tricky.

On the horizon is an adjustment to the mark scheme where students who get between a course mark of 30-39 in no more than 30 credits total per year AND has a total average mark in all courses for the year of greater than 40, will not have to resit that course (allowed fail).

What I need to do is, using data similar to the mockup attached, where each course result will be a single row, is create a calculation (or series of calculations) that will give me a yes/no statement that the student needs to resit.


Variables - contains the pass mark, credits needed per year and allowed fail range.
Marks - mockup of the main data, one row per course, multiple rows per student. Credits obtained is a calculated column that I built.

• Example student 1 - Student failed 1 course with a mark of 32, current rules would have to resit it. Future rules as it is between 30-39 and average mark is greater than 40 and as total failure is no more than 30 credits the student would have an allowed fail
• Example student 2 - Student passed all courses and would not need to resit any under current rules or future rules.
• Example student 3 - Student failed two courses, current rules would have to resit both, but they were 15 credits each totally 30 AND average mark is greater than 40 and failed marks were between 30-39 so under future rules would not.
• Example student 4 - Student failed 3 courses, all within allowed range but too many credits were failed. Student has to resit all failed courses.

I've searched and tried myself a number of times, but can't get this to work quite right. I think part of a solution would be to run a series of check columns. First check the total credits obtained was not below 90 for any student. Next a column to check that the failed mark is between the allowed range (something that looks at 'IF credits obtained = 0 THEN final course mark must be between 30-39) - excuse the non-formulaic language. Then check the average mark of all courses for that student is 40 or greater.

What I have the most trouble understanding is how to create these check columns - any advice on how to do so would be greatly appreciated. I struggle because the data is listed with each course as a row, with multiple courses per individual student. Also there may be another, more efficient method of calculating this, which I am very open to.

Kind regards,

KDDB

Hi -

I used the following formula to compute a weighted average for the Credit-Marks for the year in Column F. That formula looks like this:

=SUMPRODUCT(($A$2:$A$22=A2)*($C$2:$C$22)*($D$2:$D$22))/SUMPRODUCT(($A$2:$A$22=A2)*($D$2:$D$22))

It just takes the Mark times the number of credits, divided by the sum of the credits for that student.

Next, I put together a simple IF statement in Column G to reflect the current rules regarding re-sitting a course. That formula looks like this:

=IF(C2<40,"Resit Course","Passed")

This formula just looks at the score for each course individually and assigns Passed or Resit Course based on the Mark.

The last column, for the future rules, looks at the mark for the class and if it's above 40, the class is Passed. If the mark is less than 30, the result is Resit Class. If the course is between 30 and 39, AND the sum of the courses between 30 and 39 are less than 30 credits, then we test the weighted average to see if that is above 40. If all of the conditions are met, it results in a Allowed Fail. Otherwise, that class must be retaken (Resit Course).

=IF(C2>=40,"Passed",IF(C2<30,"Resit Course",IF(AND(SUMPRODUCT(($A$2:$A$22=A2)*($C$2:$C$22<40)*($C$2:$C$22>29)*($D$2:$D$22))<=30,F2>40),"Allowed Fail","Resit Course")))

Attached is your sample worksheet with the above formulas in place. Please modify the data to test the formulas and make sure they are working to your expectations.

Hope this helps.

Thanks for the rep! I'm a little old school and I learned my way on SUMPRODUCT. It's not as efficient as SUMIF, but I usually can program a solution in SUMPRODUCT faster and once I get the logic down I can always switch to SUMIF if I need speed (e.g., a HUGE data set with lots of array formulas or other conditions that drag down the speed of the worksheet.)

Anyway, glad I could help.