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