John Luther
New Member
- Joined
- May 5, 2014
- Messages
- 28
I am trying to setup up a larger solver model that, in addition to another of other things, solves for this problem:
I have a list of, in this case, 15 finishes in column A that I would like to divide into 5 equally sized groups (days) of 3 finishes by changing the values (1-5) in column B.
I have tried to setup a solver model with only the constraints that the range in column B be an integer <=1 and >=5, solving for a minimization of the max + average of the total for all 5 groups. The totals for each day are calculated on the worksheet as the variable cells change. The optimal solution would be 3 finishes per day, and the objective would be 6 (the max + the average). That may be a loopy way to try to evenly distribute the list, but it seems to me that it should work. But I can't get solver to give me a good solution. If I use the GRG Nonlinear option, it wants to use decimals even though I have an integer constraint. If I use the evolutionary option, it solves but not optimally - even if I use a high mutation rate and let it run for a long time. The solution below is the best I could get. I am a novice at this sort of thing, but I'm trying to learn. I just can't figure out why solver wouldn't be able to do this. Any help would be appreciated.
<colgroup><col style="mso-width-source:userset;mso-width-alt:3291;width:68pt" width="90"> <col style="mso-width-source:userset;mso-width-alt:5046;width:104pt" width="138"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:3474; width:71pt" span="5" width="95"> </colgroup><tbody>
</tbody>
I have a list of, in this case, 15 finishes in column A that I would like to divide into 5 equally sized groups (days) of 3 finishes by changing the values (1-5) in column B.
I have tried to setup a solver model with only the constraints that the range in column B be an integer <=1 and >=5, solving for a minimization of the max + average of the total for all 5 groups. The totals for each day are calculated on the worksheet as the variable cells change. The optimal solution would be 3 finishes per day, and the objective would be 6 (the max + the average). That may be a loopy way to try to evenly distribute the list, but it seems to me that it should work. But I can't get solver to give me a good solution. If I use the GRG Nonlinear option, it wants to use decimals even though I have an integer constraint. If I use the evolutionary option, it solves but not optimally - even if I use a high mutation rate and let it run for a long time. The solution below is the best I could get. I am a novice at this sort of thing, but I'm trying to learn. I just can't figure out why solver wouldn't be able to do this. Any help would be appreciated.
| FINISHES | GROUP (1-5) | GROUP: | 1 | 2 | 3 | 4 | 5 |
| EM | 3 | TOTAL: | 3 | 4 | 4 | 4 | 3 |
| TCM | 3 | Black Tea | DM | EM | OM | TMM | |
| TMM | 5 | HM | EM | TCM | Distressed AW | CSMM | |
| CSMM | 5 | Tobacco | DGK | AWM | CC | OEM | |
| TCM | 3 | AWM | DM | Old World AW | |||
| DM | 2 | ||||||
| DM | 2 | ||||||
| OM | 4 | ||||||
| Distressed AW | 4 | ||||||
| CC | 4 | ||||||
| OEM | 5 | ||||||
| OEM | 5 | ||||||
| EM | 3 | ||||||
| AWM | 3 | ||||||
| Black Tea | 1 | ||||||
| EM | 2 | ||||||
| HM | 1 | ||||||
| AWM | 3 | ||||||
| OM | 4 | ||||||
| DGK | 2 | ||||||
| DGK | 2 | ||||||
| AWM | 2 | ||||||
| AWM | 3 | ||||||
| AWM | 2 | ||||||
| AWM | 3 | ||||||
| HM | 1 | ||||||
| DM | 2 | ||||||
| DM | 2 | ||||||
| DM | 2 | ||||||
| DM | 2 | ||||||
| DM | 3 | ||||||
| Tobacco | 1 | ||||||
| DM | 3 | ||||||
| AWM | 2 | ||||||
| EM | 2 | ||||||
| EM | 2 | ||||||
| AWM | 2 | ||||||
| EM | 2 | ||||||
| HM | 1 | ||||||
| DM | 2 | ||||||
| DM | 2 | ||||||
| DM | 2 | ||||||
| DM | 2 | ||||||
| DM | 3 | ||||||
| DM | 3 | ||||||
| DM | 2 | ||||||
| Distressed AW | 4 | ||||||
| HM | 1 | ||||||
| HM | 1 | ||||||
| Tobacco | 1 | ||||||
| Tobacco | 1 | ||||||
| DGK | 2 | ||||||
| Old World AW | 4 | ||||||
| Tobacco | 1 | ||||||
| Tobacco | 1 | ||||||
| EM | 3 |
<colgroup><col style="mso-width-source:userset;mso-width-alt:3291;width:68pt" width="90"> <col style="mso-width-source:userset;mso-width-alt:5046;width:104pt" width="138"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:3474; width:71pt" span="5" width="95"> </colgroup><tbody>
</tbody>
