Hello I need help with an optimization routine<o
></o>
I have a 60 X 20 matrix of values which when combined with 20 factors generates a weighted average value for each row of 60 entries.<o></o>
I have a target range (upper lower bound) for each row and would like to quickly develop an optimization/simulation routine which would maximize the number valuesfor the 60 rows which fall within the target range for each row by changingsome of the 20 factors (e.g. fix 14 factors and select 6 factors to achieveobjective) <o></o>
Essentially trying to solve a simultaneous equation with 60equations by selecting say 6 factors which affect the 60 equations subject tothe constraint that a minimal number of weightedaverage values for each equation should fall with target range ((e.g. minimumof 50 out of 60 weighted average values in rows should lie within their target range). Abbreviated example image below
Would appreciate any help in resolving this in Excel.
<tbody>
</tbody>
></o>I have a 60 X 20 matrix of values which when combined with 20 factors generates a weighted average value for each row of 60 entries.<o
></o>I have a target range (upper lower bound) for each row and would like to quickly develop an optimization/simulation routine which would maximize the number valuesfor the 60 rows which fall within the target range for each row by changingsome of the 20 factors (e.g. fix 14 factors and select 6 factors to achieveobjective) <o
></o>Essentially trying to solve a simultaneous equation with 60equations by selecting say 6 factors which affect the 60 equations subject tothe constraint that a minimal number of weightedaverage values for each equation should fall with target range ((e.g. minimumof 50 out of 60 weighted average values in rows should lie within their target range). Abbreviated example image below
Would appreciate any help in resolving this in Excel.
| Maximise total by changing factors in green | Factor 1 | Change this Factor | Factor 20 | ||||
| Total | Weight 1 to 20 | 9.1% | 3.1% | 5.0% | |||
| Target Range | Sumproduct | 1 | |||||
| Lower | Upper | Current Value | If sumproduct in range 1 , 0 | Entry No | Cluster 1 | Cluster .. | Cluster 20 |
| 83 | 77 | 77 | 0 | 1 | 150 | 30 | 150 |
| 98 | 94 | 94 | 0 | .. | 150 | 60 | 150 |
| 50 | 60 | 76 | 0 | .. | 150 | 120 | 30 |
| 98 | 101 | 101 | 1 | 60 | 90 | 90 | 90 |
<tbody>
</tbody>
