Goal Seek Event
The Goal Seek Event Designer allows you to define an iterative calculation event with custom action hooks. In a dedicated server environment, this allows uploading a compatible .NET assembly with custom code that will be executed during the regular execution of calculations. This feature is not supported on Public Cloud accounts.
You can create multiple goal seek Events in a web application, but an Action Button can only have one Goal Seek Event assigned to it. Multiple goal seek Events must be assigned to separate Action Buttons.
Creating a Goal Seek Event
To create a Goal Seek event, go to Goal Seek Events under Events and press Add Goal Seek Event.
The Name is simply a friendly identifier provided to the Goal Seek Event to help you subsequently identify this process in contrast with other Action Button events.
Target Reference Named Range
Also known as Set cell. The Target Reference Named Range field contains the formula to test the Changing Reference Named Range against to reach the Target Value. This value must be Single Named Range containing a formula. Named ranges that are not compatible cannot be selected from the Target Reference Named Range dropdown.
Changing Reference Named Range
Also known as By changing cell. The Changing Reference Named Range must be a static value or an input cell. The system will change the Changing Reference Named Range value with every iteration to reach the Target Value.
Also known as To value. The Target Value is the number the goal seek logic will try to reach by changing the Changing Reference Named Range.
The Max Iterations value determines how many times the system will try changing the Changing Reference Named Range field to reach the Target Value. Keeping this number high will cause the calculations to take longer. If the system cannot find a result before it reaches the Max Iterations count, the goal seek event will fail.
Max Change is the precision in reaching the Target Value. If the system cannot find the Target Value within this range after as many iterations as set by Max Iterations, goal seek will fail.
If Display Error is enabled, the system will display a pop-up message in the event of goal seek failure.
Cancel on Error
If Cancel on Error is enabled, the system will cancel any other events (i.e., Print, Email etc.) if the goal seek fails to generate a result.
The example below aims to find the interest rate for loan payments within a specific time frame and loan amount. In this example, the Payment field contains a formula that calculates the monthly payment amount and the Interest Rate field is a static field. Using goal seek, you can find the interest rate that meets all conditions.
In the web application:
To find what the rate would be if monthly payments of $1,000 were accepted, Target Reference Named Range can be set to Payment, and Changing Reference Named Range to Interest Rate. The Target Value in this example is -1000.
In the web application: