Sample 2

Simple Constraints

This lesson will show you how to:

• add a constraint to a simple spreadsheet model (Sample 1).

The constraint will force GASolver to use solutions in which one value must be less than another. In this case one cell will be required to hold values that are less than a value to be specified within another cell. Cell B7 will be constrained and cell B16 will hold the constraint. Using cell references to hold constraints is slightly more complex than using a constant value but of course it allows your models to be more flexible.

To start Sample 2:

1. Run GASolver by clicking the GASolver desktop icon.

2. Open Sample2.xls from the folder where you installed GASolver (the default folder is c:\program files\yearstretch GASolver\sample1.xls) OR run Start | Programs | Yearstretch GASolver | Sample2.

3. Click the lower button to add a new output cell constraint.

4. Click the button at the right of the Constrain Value in Cell Reference box.

5. Click the work sheet cell B7. The address will appear in the floating box. This is the cell that will be constrained.

6. Click OK to return the value to the Constraint dialog.

/

7. Use the pull down menu to change the 'to be' constraint type to be 'Less than'.

8. Click the button at the right of the Cell Reference box.

9. Click the work sheet cell B16. The address will appear in the floating box. This is the cell that will be constrained.

10. Click OK to return the value to the Constraint dialog.

11. Click the button.

You will see the constraint conditions summarised in the Constrained cells list. To change a constraint see

12. Click the button.

    We must now set the conditions under which GASolver will halt optimising. For the time being ignore the Start Point settings.

    

13. Change the options to accept an answer Target Cell Value Is Within 0.1 of our target value (i.e. 499.9 - 500.1)

14. Change the When Generations Reach limit to 500.

15. Change the Processing Time limit to 5 minutes.

16. Click the button. Solving will start.

You will see progress dialog.

You may stop the process at any time by clicking the Pause button.

You will see both the input changeable cells and the output results cell assume differing values in the spreadsheet as the process works towards the goal.

Version Variation: Early Excel versions may not halt immediately after you press the Pause button but will halt eventually, Excel 2000, 2002 (XP) and above will halt immediately.

When GASolver has located a solution it will show its results dialog

• If you wish to keep the solution values found then select Keep the solution found.

• If you would rather abandon the solution and restore the starting values then select to Restore the previous values.

• If you would rather like to continue to search for another then select Continue solving from here.

From this Example Lesson 2 you have learnt to tell GASolver how to select solutions that where input cells meet certain conditions only.

The settings for Example Lesson 2 are contained in the Sample2.txt file which may be loaded into GASolver by pressing the  button from the main dialog and locating the folder where you installed GASolver (the default folder is c:\program files\yearstretch gasolver).

See also Sample 1, Sample 3, Adding Constraints, Changing Constraints, Using Constraints