Creating a performance scoring system

**Tony89** · 07-13-2012, 02:18 AM

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

**TMS** · 07-13-2012, 03:26 AM

Simplistic approach: =IF(B3=0,0,IF(B3<=5,5,IF(B3<10,10,15)))

Not very scalable, but, for what you want, it should work.

Regards, TMS

**Tony89** · 07-13-2012, 04:11 AM

Thanks, TMS appreciate it the help. How can I make it so that it collates the data for each student (i.e. when someone appears twice such as Joe Bloggs above, it adds the two scores for a final total).

Also,is it possible to then create a new spreadsheet which adds this data from all the monthly excel spreadsheets and compiles a yearly average score for each student?

Thanks again, Tony

**TMS** · 07-13-2012, 04:29 AM

Was your original question answered? If so, the thread should be marked as solved.

Your follow up sounds like two different questions and probably warrant at least one, possibly two new threads.

Collate where? Any other criteria? Look at SUMIF or SUMIFS or, in a worst case scenario, SUMPRODUCT.

The more workbooks you have, the more complicated the solution becomes. I would suggest that you have *one workbook* for the year, with *one sheet* with ALL raw data and use Filters and Pivot Tables for individual, monthly and yearly analysis.

regards, TMS

**Tony89** · 07-13-2012, 05:13 AM

Thanks again for your help. My original question was not fully solved because I need the code to add all the scores for the student in the cases where the student's name appears more than once (e.g. Joe Bloggs in the case above). I'd like to put it a table below the data I have with all the students' names and their scores. Problem with the one provided is it only calculates the score for one instance of overdue work, I need to add these scores for multiple so I can put it all in one box under the student's name.

I take your point about creating a new thread for the other question. Thank you, Tony

**TMS** · 07-13-2012, 05:22 AM

Fair enough, I didn't notice the last part and there was no indication of that requirement or where you expect it to be ... but, as outlined, you can use SUMIF to total the scores. However, I still think you would be better having all the raw data in a single worksheet and use filters to look at entries for an individual and/or use a Pivot Tables for the monthly and Yearly analyses.

Regards, TMS