Hello to all, first post here. If anyone has done anything in the past with ranking within groups in Excel then you may recall a lot of processing power is required to do it with large numbers of rows. Here's a hypothetical in my particular case, let's say we have data from ten years ago, test scores from a couple of thousand classrooms with maybe 20 students in each class. Each student is required to take five separate exams, one history exam, one math exam etc. After taking the exams I have ranked each student's score on each exam within that student's particular classroom. So here's what I have for each student, ClassroomID, StudentID, RankOnTest1,RankOnTest2,RankOnTest3,RankOnTest4,RankOnTest5.

I want to use solver to find optimum weights on each exam for the highest ranking scores. As a starting point let's say a student's RankOnTest1 = 1, let's give 20 points for that and for the second best rank on that test let's start with 15 points and so on down the line to maybe the top five ranks get some points. Sum the points for each student on all five tests. Next, rank the students by that sum within each classroom, this particular ranking operation because it's done within groups (ClassroomID) is where the processing overhead occurs.

Once we have the rankings, I want to essentially nominate the top ranked student within each class as the most likely to succeed. Now I have another column with current salary for each student ten years after graduation. For those students most likely to succeed I want to average their current salary. This salary is the cell I want solver to maximize. The cells I want solver to change are the points assigned to the ranks of the exams. The problem is, when the points change, the ranks change, this takes time and solver just seems to hang.

Has anyone faced a similar problem? Would some tool other than solver be more appropriate in this scenario?