Hi, I'd really appreciate any guidance.

I want to create something which measures the performance of students with respect to late submission of work. The scoring system will be based on data from an excel spreadsheet. There are two ways in which the students’ performance may be measured from this spreadsheet:

1. The frequency with which their name appears in the report (i.e. the number of times their name appears under in the column “Student”)
2. The number of “Hours Overdue” for each instance that the student appears in the report

A simple scoring system would be something like:

• 5 points for each time their work is 1-5 hours overdue
• 10 points for each time their work is 6-10 hours overdue
• 15 points for each time their work is >10 hours overdue

A higher score indicates a worse performance. An example of the relevant data would be:

Scoring for late submission.xlsx

Or:

Hours Overdue Student
5 Andrew McKenzie
34 Joe Bloggs
9 John Smith
3 Joe Bloggs
23 Jane Doe

So the scores for these students would be:
Andrew McKenzie - 5
Joe Bloggs - 20
John Smith - 10
Jane Doe - 15

How would I go about programming something to perform this function, either in excel or using a different application? Thanks for your help