Previously, I have made a brief introduction to the Evolutionary engine when discussing GRG engine. Today, I’d like to show you how to use Evolutionary Engine to solve a problem in real life.
1. Functions to be used in this article
Before showing you the problem and how to solve it, I’d like to introduce several functions.
1.1 HLOOKUP function
Generally speaking, HLOOKUP function searches for a value in the top row of a table or an array and then returns a value in the same column from a row you specify in the table or array. Look at Figure 1.1. Suppose that we’d like to search value 1 (from cell C4) in the first row (D3:G3) of the table (D3:G5), in which column can we find value 1? The answer is “Column D”. Now let look at Column D. There are 3 values in total. What value will you get if you want to retrieve data from the second row (enclosed by yellow square) of the table? It is 9.
Here is the syntax for HLOOKUP function:
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) | |
lookup_value | Required. The value to be found in the first row of the table. Lookup_value can be a value, a reference, or a text string. |
table_array | Required. A table of information in which data is looked up. Use a reference to a range or a range name. |
row_index_num | Required. The row number in table_array from which the matching value will be returned. A row_index_num of 1 returns the first-row value in table_array, a row_index_num of 2 returns the second-row value in table_array, and so on. |
range_lookup | Optional. A logical value that specifies whether you want HLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, HLOOKUP will find an exact match. If one is not found, the error value #N/A is returned. |
You will find that lookup_value in our example is 1, table_array is D3:G5 and row_index_num is 2. Since we want to get an exact match, the range_lookup is FALSE. In summary, the whole function is “HLOOKUP ($C4, $D$3: $G$5, 2, FALSE)”. Perhaps you have already noticed that the formula in Figure 1.1 is “=HLOOKUP ($C4, $D$3: $G4, $B4+1, FALSE)” which is slightly different from ours. By replacing “2” with “$B4+1”, we can easily copy the formula to cells below H4. This can save us time. Similarly, “$D$3:$G4” will also change as we copy it cells below H4.
1.2 COUNTIF function
The COUNTIF function in Excel counts the number of cells in a range that matches the supplied criteria. Here is the syntax for COUNTIF function.
COUNTIF (range, criteria) | |
range | The group of cells you want to count. The range can contain numbers, arrays, a named range, or references that contain numbers. |
criteria | A number, expression, cell reference, or text string that determines which cells will be counted. |
Suppose that we want to count a number of cells equal to 1 in table C3:C9, we can use the formula “=COUNT ($C$3: $C$9, 1)”. In order to incorporate flexibility, you can see from Figure 1.2 that I replaced “1” with “$E3”. By copying this formula into cells F4 through F6, we can count a number of cells equal to 1, 2, 3, and 4 respectively.
1.3 OR function
OR function returns TRUE if any argument is True. It returns FALSE only when all arguments are False. Look at the formula in Figure 1.3. There are two arguments in OR function: C3 < 8 and C3 > 12. Both arguments are FALSE since C3 equals to 12 which is greater than 8 and less than 12. Therefore, the formula returns FALSE. By copying this formula into cell D4, you will get “True” as 13 is greater than 12. The second argument “C4 > 12” is True.
1.4 IF function
IF function can make logical comparisons between a value and what you expect, then return something you specified in function per comparison results. The syntax is IF (comparison, do something if True, do something else if False). It will return the second argument if the first argument is True. Otherwise, it will return the third argument. Let’s look at the formula in Figure 1.4. The first argument “OR (C3<8, C3>12)” is FALSE and thus Excel returns 0 in cell D3. By copying this formula into cell D4, you will get 1 as the first argument “OR (C4<8, C4>12)” is True.
2. Problem to be solved in this article
Suppose that you need to assign 40 employees to four Departments. The head of each department has rated each employee’s competence on a 0 to 10 scale (10 equals most competent). Each employee has rated his satisfaction with each job department (again on a 0 to 10 scale). Data are recorded in Problem worksheet. You are going to assign between 8 to 12 people to each department. How can you assign employees to workgroups to maximize total satisfaction and ensure that each division has the required number of employees?
3. Set up Model for above problem
The employee ID was put in cells from B3 through B43. Scores given by the head of each department for each employee were entered into range D4:G43. And scores given by employees to the job of each department were filled into range H4: K43. Cells from C4 through C43 are our By Changing Cells and indicate which departments the employees will take part in.
Copying formula “=HLOOKUP ($C4, $D$3: $G4, $B4+1, FALSE)” from L4 to L5:K43 enables you to look up each employee’s qualifications for his/her department. Next, copying formula “=HLOOKUP ($C4, $H$3: $K4, $B4+1, FALSE)” from M4 to M5:M43 enables you to look up the employee’s satisfaction with his/her department. Our objective is to maximize employee’s satisfaction and therefore formula “=SUM (M4: M43)” was entered into cell P10 to calculate total satisfaction.
To make sure that each department has between 18 and 22 employees, we need to count how many employees have been assigned to each division. This can be done by copying formula “=COUNTIF ($C$4: $C$43, $O5)” from P5 to P6:P8. Formula “=IF (OR (P5<8, P5>12), 1, 0)” was copied from cell Q5 to Q6: Q8 to determine if the number of employees for each department is correct. If the numbers of employees are correct, the value should be 0 for all cells from Q5 through Q8. This is one of our constraints. Another constraint is an implicit one. There are only 4 departments and therefore values should be 1, 2, 3, or 4 for cells in range C4:C43. We can use three formula to express this constraint: “$C$4: $C$43 <= 4”; “$C$4: $C$43 >= 1”; “$C$4: $C$43 = integer”.
4 Use Excel Solver to solve problem
Click on Solver in Analysis group to open Solver Parameters dialog box. Fill the Solver Parameters dialog box as shown in Figure 2.2. You can see that Evolutionary engine was used here.
After clicking on Solve, you will get results as below. It tells that Worker 1 and 2 should be sent to Department 1. Worker 3 needs to be sent to Department 3.Remark
If you look at Solver Results (Figure 2.2) dialog box closely, you will notice that there is a message. It suggests you try a smaller convergence setting or a different starting point. You can do it on your own and see if the values in range C4:C43 will change.
Read More…
Sequencing problem using Johnson’s algorithm of scheduling n-jobs on 2-machines [Sol]
GRG Multistart and Evolutionary Excel Solver Engines [2 Case Studies]
How to Use Solver in Excel (Solving Linear Programming Problems)!
Download working file
Download the working file from the link below.