You'll see the optimal number of employees for each day. You can also selected the options Assume Linear Model and Assume Non-Negative for the changing cells by clicking Options in the Solver Parameters dialog box and then selecting the check boxes in the Solver Options dialog box.Ĭlick Solve. To add this constraint,click Add in the Solver Parameters dialog box and enter the constraint in the Add Constraint dialog box (shown below). The constraint A5:A11=integer ensures that the number of employees beginning work each day is an integer.
The constraint C12:I12> =C14:I14 ensures that the number of employees working each day is at least as large as the number needed for that day. In the objective cell (A3), you want to minimize the total number of employees. This total is the number of people working on Monday.Īfter computing the total number of employees in cell A3 with the formula = SUM(A5:A11), you can enter your model in Solver as shown below. For example, in cell C12, this formula evaluates to = A5+A8+A9+A10 +A11, which equals (Number starting on Monday)+ (Number starting on Thursday)+(Number starting on Friday)+(Number starting on Saturday)+ (Number starting on Sunday).
To compute the number of employees working each day, copy the formula = SUMPRODUCT($A$5:$A$11,C5:C11) from C12 to D12:I12. For example, the 1 in cell G5 indicates that employees who started working on Monday are working on Friday the 0 in cell H5 indicates that the employees who started working on Monday are not working on Saturday. The value 1 in a cell indicates that the employees who started working on the day designated in the cell’s row are working on the day associated with the cell’s column. To track the number of employees working each day, enter a 1 or a 0 in each cell in the range C5:I11. Enter each day’s required workers in the range C14:I14. For example, in A5, enter 1 to indicate that 1 employee begins work on Monday and works Monday through Friday.
#Process layout excel solver trial#
Begin by entering trial values for the number of employees who start their five-day shift each day in the cell range A5:A11. To set up the model, you need to track the number of employees working each day. ( Number of employees working) >=( Needed employees) Each changing cell must be a non-negative integer.Ĭonstraints – For each day of the week, the number of employees who are working must be greater than or equal to the number of employees required. Objective cell – Minimize the total number of employees.Ĭhanging cells – Number of employees who start work (the first of five consecutive days) each day of the week. Note: The objective cell is called the target cell in Excel 2007.
0 kommentar(er)
