Solver Event is an optimization tool that can be used for what-if analysis. The algorithm uses multi-start with random seed generation for global optimization.
This feature is not supported on Public Cloud accounts.
Creating a Solver Event
To create a Solver event, go to Solver Events under Events and press Add Solver Event.
The Name is simply a friendly identifier provided to the Solver Event to help you subsequently identify this process in contrast with other Action Button events.
The Objective field contains the formula to test the Variable Cells against to fulfill the optimization. This value must be Single Named Range containing a formula. Named ranges that are not compatible cannot be selected from the Objective dropdown.
The Optimization Type determines the direction of the solving.
- Maximum: The Solver Event will try to find the largest value for the Objective.
- Minimum: The Solver Event will try to find the smallest value for the Objective.
- Value Of: Select this if you want the Objective reflect a certain value.
Value of Goal
Enter the specific value you want to reach. This field will be visible only if Value Of option is selected.
Mark this option if all your constraints are non-negative numbers (equal or greater than 0). If this option is marked, you do not have to set a lower bound to your variables unless there are variables which have different lower bounds.
Display Result Message
If Display Result Message is enabled, the system will display a pop-up message about the result.
Cancel on Error
If Cancel on Error is enabled, the system will cancel any other events (i.e., Print, Email etc.) if the Solver fails to generate a result.
The Variables are the non-formula, single named range cells that contain variable data that can be changed to achieve the objective. Use Add/Delete buttons to insert and manage the variable list.
The Constraints are simply the conditions that must be met. These conditions are basic mathematical equations along with setting variables as integers.
You can use Add/Edit/Delete buttons to insert and manage the constraint list.
The Add and Edit buttons pops up a dialog with three fields:
- Named Range: A dropdown allows you to select single named ranges. This is the left side of a condition.
- Operation Type: A dropdown with mathematical operators and Integer Note: The Integer option is available only for the assigned variables.
- Value: A field that you can enter a numerical value which will be the right side of the condition. This field will be hidden if the Operation Type is selected as Integer.
Update Solver Event
The button allows you to save the Solver Event.
The example below aims to find the number of packages to ship 480 products. There are three types of packages. Each type of packages should be used at least 1 time and each package is stored in limited numbers:
- 10-item package, 5 available
- 30-item package, 5 available
- 50-item package, 8 available
According to this information, we can model our example as the following:
We want the Total (C20) to be equal to 480 while package numbers are staying between limits. To solve this problem, you can use the Solver Event like the following:
There are two important steps in the User Interface module, after creating a Solver Event:
- Each variable in the Solver Event should be bound to an input control in the User Interface.
- A Solver Event must be assigned to a button to be triggered. Only a single Solver Event can be assigned to one button. Multiple buttons is needed to run multiple Solver Events.