Hi everyone, I'm new to this forum and the world of excel. Appreciate the all the help in advance. Thank you so much!
I am stuck with a work problem to automate a recommendation system for matching tutor recommendations to students. (students will choose from a list of recommended tutors)
Problem Background
Data Sets
Problem 1: Repeated random generation
<tbody>
</tbody>
By splitting the allocation directly into W/X/Y/Z & male/female conditions, I was able to do an index match from the matchmatrix to randomly select valid matches from within these criteria for a specific student.
e.g. for Student 1, randomly select valid W male tutor
------------------------------------------------------------------------------------------------------------------
Index( MatchMtx!TutorNameColumn,
Small(
IF(
If(Tutor = W, 1, 0) + IF( Gender = male, 1, 0) + IF( Status = active, 1, 0)
+ IF( indexmatch of tutor validities for Student 1 = 1 {aka valid pair in matchmatrix}, 1, 0) = 4 {4 conditions},
ROW( MatchMtx!TutorNameColumn) - MIN(ROW( MatchMtx!TutorNameColumn))+1) {I believe this part converts excel row position to array row position}
{returns array of row numbers that fit all 4 conditions above},
RANDBETWEEN(1, COUNTIFS(the 4 conditions above)
{random generation of position of the array above for the small function to operate}
------------------------------------------------------------------------------------------------------------------
Thus the problem arises from the 4th column. where the 3rd W tutor is either male or female.
I am not sure how to proceed with ensuring this tutor does not repeat from the other W tutors considering the randomization occurs simultaneously every time i press F9 to refresh it. I found a possible solution that uses MRAND but for my organization, adding in extra addins are not an option.
Problem 2: Fair allocation
Ideally, we would like to ensure each individual tutor is recommended equally to students for choosing (no need exact but close enough within say a + 5% margin from mean) as compared to their equivalent peers (5 students, 10 male W tutors, each male W tutor recommended to 2 students), can't have a certain few tutors over-taxed on fielding or even accepting requests.
I am able to calculate frequencies of recommendation but i am unsure how to either:
a) incorporate it into the randomization, again simultaneous calculation
b) run a solver to iterate randomization until optimal solution is found (i have 0 experience with solver)
I do understand problem 2 is really complex and it would really help cut down the time spent refreshing and copy pasting possible solutions to get a desired global solution
There is also a Problem 3 regarding minimizing repeated combinations of tutors but probably even more complex than problem 2.
(Concern is that combination repeats might yield similar comparative judgements that result in some tutors in that combination being over-selected by students)
I really appreciate all the help I can get regarding this as I've been stuck on the problem for a while now. I am open to new solutions that revamp the entire recommendation process considering it breaks down the process into various stages, splitting by allocation criteria etc.
Thank you so much once again!
Sincerely yours,
Hans
I am stuck with a work problem to automate a recommendation system for matching tutor recommendations to students. (students will choose from a list of recommended tutors)
Problem Background
- 4 types of tutors (W / X / Y / Z)
- 9 Tutor choices offered / student (3W + 2X + 2Y + 2Z)
- Gender spread equally
Data Sets
- List of students
- List of tutors
- matchmatrix (tutors in rows, students transposed into columns):
basically a matrix of 1's and blanks based on whether each tutor/student pair on the matrix meets the criteria for a valid match, 1= yes blank = no
Problem 1: Repeated random generation
| Student | W(male) | W(female) | W(male/female) | X(male) | X(female) | Y(male) | Y(female) | Z(male) | Z(female) |
| Student1 | random valid male W tutor for student 1 | ||||||||
| Student2 | |||||||||
| Student3 | |||||||||
| Student4 |
<tbody>
</tbody>
By splitting the allocation directly into W/X/Y/Z & male/female conditions, I was able to do an index match from the matchmatrix to randomly select valid matches from within these criteria for a specific student.
e.g. for Student 1, randomly select valid W male tutor
------------------------------------------------------------------------------------------------------------------
Index( MatchMtx!TutorNameColumn,
Small(
IF(
If(Tutor = W, 1, 0) + IF( Gender = male, 1, 0) + IF( Status = active, 1, 0)
+ IF( indexmatch of tutor validities for Student 1 = 1 {aka valid pair in matchmatrix}, 1, 0) = 4 {4 conditions},
ROW( MatchMtx!TutorNameColumn) - MIN(ROW( MatchMtx!TutorNameColumn))+1) {I believe this part converts excel row position to array row position}
{returns array of row numbers that fit all 4 conditions above},
RANDBETWEEN(1, COUNTIFS(the 4 conditions above)
{random generation of position of the array above for the small function to operate}
------------------------------------------------------------------------------------------------------------------
Thus the problem arises from the 4th column. where the 3rd W tutor is either male or female.
I am not sure how to proceed with ensuring this tutor does not repeat from the other W tutors considering the randomization occurs simultaneously every time i press F9 to refresh it. I found a possible solution that uses MRAND but for my organization, adding in extra addins are not an option.
Problem 2: Fair allocation
Ideally, we would like to ensure each individual tutor is recommended equally to students for choosing (no need exact but close enough within say a + 5% margin from mean) as compared to their equivalent peers (5 students, 10 male W tutors, each male W tutor recommended to 2 students), can't have a certain few tutors over-taxed on fielding or even accepting requests.
I am able to calculate frequencies of recommendation but i am unsure how to either:
a) incorporate it into the randomization, again simultaneous calculation
b) run a solver to iterate randomization until optimal solution is found (i have 0 experience with solver)
I do understand problem 2 is really complex and it would really help cut down the time spent refreshing and copy pasting possible solutions to get a desired global solution
There is also a Problem 3 regarding minimizing repeated combinations of tutors but probably even more complex than problem 2.
(Concern is that combination repeats might yield similar comparative judgements that result in some tutors in that combination being over-selected by students)
I really appreciate all the help I can get regarding this as I've been stuck on the problem for a while now. I am open to new solutions that revamp the entire recommendation process considering it breaks down the process into various stages, splitting by allocation criteria etc.
Thank you so much once again!
Sincerely yours,
Hans
