# Optimisation for Resource Allocation

Copper Contributor

# Optimisation for Resource Allocation

Hello!

I'm currently trying to process data in Excel, and looking at how I might optimise my distribution of resources.

Effectively, I have a number of students who have provided up to 3 preferences for a staff member to talk to for guidance, and I'm looking if there's a way to optimise my spread for the most equal distribution of students per staff member.

Some staff members have been requested more than others, which is unavoidably going to lead to them having larger loads, but trying to distribute as evenly as possible. Is there a solution I'm not thinking of right now? I have the ability to process this data in either 365 or the desktop app.

2 Replies

# Re: Optimisation for Resource Allocation

o optimize the distribution of students to staff members in Excel based on their preferences and achieve the most equal distribution possible, you can use a combination of Excel features such as formulas, sorting, and potentially some manual adjustments. Here is a step-by-step guide to help you through this process:

Step 1: Gather and Organize Data

1. Create a Table for Student Preferences:
• Columns: Student Name, Preference 1, Preference 2, Preference 3.
• Rows: Each row represents a student.
2. Create a Table for Staff Members:
• Columns: Staff Member Name, Assigned Students (this will be populated later).
• Rows: Each row represents a staff member.

Step 2: Assign Students Based on Preferences

1. Add Helper Columns for Counting:
• In your staff members table, add a column for counting the number of students assigned to each staff member.
2. Use a Formula to Assign Students:
• You can use the COUNTIF and INDEX-MATCH formulas to create an initial assignment of students to staff based on their first preferences.

Step 3: Optimization Process

1. First Preference Assignment:
• Assign students to their first preference.
• For each student, check if their first preference staff member is under the average load.
• If yes, assign the student to that staff member.
• If no, move to the next preference.
2. Second and Third Preferences Assignment:
• Repeat the process for second and third preferences, ensuring you check the load of the staff members before assigning.

Example Formulas and Steps

1. Count Number of Students per Staff Member:
• In the Staff Members table, add a column (e.g., Assigned Students) to count the number of students assigned to each staff member:

=COUNTIF(StudentTable[Preference1], StaffMemberName) + COUNTIF(StudentTable[Preference2], StaffMemberName) + COUNTIF(StudentTable[Preference3], StaffMemberName)

1. Assign Students to First Preference:
• In a new column next to your Student Preferences table, use a formula to check if the staff member is under the average load:

=IF(COUNTIF(StaffTable[StaffMemberName], StudentTable[@Preference1]) < AVERAGE(StaffTable[AssignedStudents]), StudentTable[@Preference1], IF(COUNTIF(StaffTable[StaffMemberName], StudentTable[@Preference2]) < AVERAGE(StaffTable[AssignedStudents]), StudentTable[@Preference2], StudentTable[@Preference3]))

1. Manual Adjustments:
• After the initial formula-based assignment, you may need to manually adjust some assignments to ensure an even distribution.

Advanced Option: Solver Add-In (Excel Desktop)

If you are using the desktop version of Excel, you can leverage the Solver Add-In to optimize the allocation. Solver is a powerful tool for what-if analysis, and it can help find the optimal solution for resource allocation problems.

1. Enable Solver Add-In:
• Go to File > Options > Add-ins > Excel Add-ins > Solver Add-in.
2. Set Up Solver Parameters:
• Define your objective: Minimize the maximum number of students assigned to any single staff member.
• Define constraints: Each student must be assigned to one of their preferences.
• Use binary variables to represent whether a student is assigned to a staff member.
3. Run Solver:
• Run Solver to find the optimal distribution of students to staff members based on the defined objective and constraints.

Conclusion

By using these methods, you can optimize the distribution of students to staff members in Excel to achieve a more equal load. Start with the basic formula approach for initial assignment and use Solver for a more advanced and precise optimization. This approach ensures that student preferences are considered while aiming for an even distribution of assignments.

NOTE: Since no one has answered it for at least one day or more, I entered your question in an AI (Otherwise it would have cost the author a lot of writing time ). The text and the steps are the result of AI. Maybe it will help you further in your project, if not please just ignore it.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

# Re: Optimisation for Resource Allocation

@wjhaw0   This reminded me of another post I helped on:

for that post I created a Lambda function and thought it might be of help to you.  Here is the explanation I gave there and the file attached:

So in the attached I created a complex set of LAMBDA functions include 2 that are recursively calling themselves to solve this challenge.  In the attached workbook is an example:

The inputs to the main LAMBDA call include the RANGE with the selection table (must be 1 column as a unique ID followed by any number of picks) and a Table with the list of options and corresponding max # spots for that option.

This solution will:

A) randomize the order of the table

B) go down the 1st choices and allow up to the max # for each option

C) continue with each next lower choice columns

D) For EACH row IF that row did not match (i.e. all selected options were already max) then it will auto assign a non-full option based on which ever option has most spots open first. (an example of this is the last line in the image above where E was given even though they didn't select E as an preferred option)

E) Undo the randomized order so that the result correctly lines back up

F) I added in an addition output column to show the 'randomized' order that was used during the process (i found this helpful just for my sake to understand why/how each row ended up getting the choice they got)