Overview

The Solver Event is an optimization tool that can be used for what-if analysis. The tool uses a nonlinear solving method like Excel Solver's GRG Nonlinear. The algorithm uses multiple variables using random seed generation or 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.

Name

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.

Objective

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.

Optimization Type

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 value specific value you want to reach. This field will be visible only if Value Of option is selected.

Non-Negative

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.

Variables

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.

Each variable named range/cells should be associated with an input control in the User Interface.

Constraints

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 option. 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.

Note: An upper and lower bound should be added as a constraint for each variable. You can skip lower bound (greater than or equal) if the Non-Negative setting is enabled.

Update Solver Event

The button allows you to save the Solver Event.

Example

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) is equal to 480 while package numbers are staying between limits. To solve this problem, you can use the Solver Event like the following:

User Interface