How to Perform Employee Scheduling Algorithm in Excel (With Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

We can perform employee scheduling algorithm in Excel using the in-built Solver feature. This algorithm is particularly handy when it comes to efficient workforce management for an organization.

We will perform an employee scheduling algorithm for a hospital. First, we have to create a dataset. Then we will create a result outline and insert a suitable formula. And, lastly, we will carry out the analysis with the Solver feature.


Step 1: Necessary Assumptions for Employee Scheduling Algorithm

For conducting the employee scheduling Algorithm, we must make some assumptions.

  • Let’s take a dataset of five nurses and the number of hours they are willing to work each day. Also, they might not work every day.
  • In row number 10, we have put the minimum number of nurses required for each day.

Employee Scheduling Algorithm Excel

  • Now, let’s modify this dataset.
  • Create a data table of nurses and set 0 in every cell like the image below.

  • This hospital we are working with provides a minimum of two holidays for their employees.
  • Add a column and a row like the below image.
  • In Column J we will find the total number of days each employee has to work in a week.
  • And, Row 17 for the number of employees required each day.
  • For this analysis, we also have to insert an Objective box to show the total number of employees working hours in a week.

Read More: How to Create Betting Algorithm in Excel


Step 2: Using SUM AND SUMPRODUCT Functions for Employee Scheduling Algorithm

For this analysis, we have to set some formulas. We are going to use SUMPRODUCT and SUM functions as formulas.

Let’s see them.

We want to find out the maximum possible working hours of the employees in a week.

  • So, write the following formula in K5 and press ENTER.

=SUMPRODUCT(C12:I16,C5:I9)

Here,

  • C12:I16 = array1
  • C5:I9 = array2

Employee Scheduling Algorithm Excel

Upon entering the formula, the cell should give 0.

  • Now we will use the SUM formula so that we can find the number of days each employee has to work in a week.
  • So, write the following formula in J12 and press Enter.

=SUM(C12:I12)

Here,

C12:I12 = Data Range

  • Then Hold and Drag the J12 cell downward to copy this formula for all the cells.

  • Again, we have to set SUM formula in Row 17 so that we can find the number of nurses working each day.
  • So, write the following formula in C17 and press ENTER.

=SUM(C12:C16)

Here,

C12:C16 = Data Range

  • Then Hold and Drag the C17 rightward to copy the formula in all the cells.

Employee Scheduling Algorithm Excel

Thus, our dataset is ready for analysis.

Read More: How to Use Fuzzy LOOKUP Algorithm in Excel


Step 3: Applying the Solver Feature in Excel

Now let’s analyze the dataset with the in-built Solver feature.

  • First, go to the Data ribbon and click on the Solver icon.

So, a Solver Parameters window will open.

  • Now we have to set these solver parameters which is K5 in this case.
  • Set the Objective cell in the Set Objective box.
  • Keep the To section Max.
  • Insert the data array $C$12:$I$16 in the By Changing Variable Cells box.
  • Also, we have to set three constraints.
  • So, click on Add.

  • This constraint is for finding the employee’s working days. We want to know whether an employee works on a given day or not. We want to get the value in binary. 1 will mean they have to work and 0 will mean their holiday.
  • So set the Cell Reference to $C$12:$I$16, choose the bin (binary) option from the adjacent box, and press OK.

Employee Scheduling Algorithm Excel

  • The constraint will appear in the Subject to the Constraints box.
  • Now, click on Add for another constraint.

  • This constraint is for the number of employees required each day which will be equal to the data range C10:I10.
  • So, set the data range $C$17:$I$17 in the Cell Reference box.
  • Then choose the = (equal) sign in the adjacent box.
  • And, set $C$10:$I$10 in the Constraint box and press OK.

  • The new constraint will show up in the box.
  • Now, we will add the last constraint.
  • Click on the Add option.

Employee Scheduling Algorithm Excel

  • This constraint is for setting the maximum working days for each employee in a week.
  • So set the data range $J$12:$J$16 in the Cell Reference box.
  • Choose the < = (less or equal) sign in the adjacent box
  • And lastly set the Constraint to 5 and press OK.

  • Thus, we have set all the constraints.
  • Now, choose the Simplex LP option from the Select a Solving Method box and click on the Solve button.

  • Doing so, the Solver Results window will open.
  • Make sure the Keep Solver Solution is active and press OK.

Employee Scheduling Algorithm Excel

  • Therefore, the solver will give you results.
  • The Objective Cell indicates the number of working hours that the organization can get out of its employees.
  • The data range C12:I16 shows the working days of each employee in a binary form.
  • The data range J12:J16 shows the number of working days in a week for each employee.
  • The data range C17:I17 shows the number of nurses assigned on each day.


Download Practice Workbook

You can download and practice this workbook.


Conclusion

Thank you for making it this far. We have shown you how to perform employee scheduling algorithm in Excel. Hope you find the content of this article useful. If there are further queries or suggestions please do mention them in the comment section.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sourav Kundu
Sourav Kundu

Sourav Kundu, BSc, Naval Architecture and Marine Engineering, BUET, Bangladesh, has been a part of the ExcelDemy project for 1 year. He has written more than 70 articles and 12 comments for ExcelDemy. Currently, he is working as a Exceldemy team member writing contentful articles. His work and learning interest revolves around data analysis. He has been working Microsoft Office Suites, and MS Excel for Data Analysis, and developing Excel Applications. Besides he has been working on Excel VBA and Excel Dashboard to learn more about data analysis.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo