Solve functions (Xsolve)

Spark solver to perform a What-If Analysis similar to Goal Seek at each API call. This is useful to determine the needed value to achieve a target input. For example, Goal Seek could be used to determine the how long it would take pay off a certain loan amount.

Map the solve

Syntax: Xsolve_<parameter>

To build a solve we suggest to use the Coherent Assistant has a Solver feature to create, manage and trace solves or start with the Example of Xsolve mapping sample file.

Users can build a simple solver that works similar to Excel’s Goal seek function in order to perform a What-If Analysis. This solver is recognized by Spark and returns the forecasted value.

• For example, to reach a specific goal for the amount of annual premium paid by changing the sum assured. This is an effective method to find out how much an individual can be assured for, with a premium constraint.

• If a Spark Service has multiple solves, they will be executed in alphabetical order.

Ensure that thorough testing is performed on the domain of inputs to ensure that the Xsolve is providing the correct calculations.

Adjust the solve parameters

Key	Value
Run if	A TRUE or FALSE value indicating whether or not this solve should be executed.
Target cell	A link to the formulated cell that needs to reach the Target value. This is generally recommended to be defined as a difference between the desired Target value of 0.
Target value	The value of the Target cell to achieve. This is best set to 0.
By changing	A link to the cell that has to change in order to for the Target cell to reach the Target value.
Solve algorithm	The available options are SECANT and BRENT. We recommend using BRENT as it is able to solve more complex workbooks accurately.
Max change	Maximum acceptable value of |Solve result - Target value| within Max iterations, default 1.
Max iterations	Maximum iterations for the number of solve.
Initial guess	Provide an initial guess to help reach the Target value sooner.
Lower bound	For the BRENTalgorithm, the bounds of the solve can be defined at the start of the solve. When this is provided, the Initial guess is ignored.
Upper bound	For the BRENTalgorithm, the bounds of the solve can be defined at the start of the solve. When this is provided, the Initial guess is ignored.
Solve started	TRUE or FALSE value. Spark will write this into the cell during execution. This enables downstream calculations to use this value.
Solve target	This is provided in order to help assess how close the solve was in reaching the target, given the solve can conclude within the Max change. Spark will write this into the cell during execution. This enables downstream calculations to use this value.
Solve iteration	Spark will write this into the cell during execution. This enables downstream calculations to use this value.
Solve result	This will contain either the successful solve value or #N/A. Spark will write this into the cell during execution. This enables downstream calculations to use this value.
Solve successful	TRUE or FALSE value. Spark will write this into the cell during execution. This can be useful in taking the initial solve result and applying a transformation on the solved result. For example, IF the solve is successful, take the By changing value and ROUND the result.

Errors

• #CALC! is returned for the Solve result if:

  • The Target value, Initial guess, Max change, or Max iterations are not a proper number.

  • The maximum iterations are achieved.

• #NUM! will be returned for the Solve result if the solve algorithm encounters a zero slope error.

Trace solve iterations

The Xsolve iterations can be shown by using the Execute API (v3) request_meta debug_solve.

Example of Xsolve mapping

This service can be uploaded to Spark to use as a simple example. It does not include all of the optional parameters that are described above.

22KB

Xsolve.xlsx

Open

Solve algorithm reference

BRENT

The implementation aligns with the Brent's method. The Coherent implementation will bound the independent variable between 0 and Initial guess * 100. This can be overridden using the Lower boundand Upper boundparameters.

SECANT

The implementation is inspired by the Secant method. The attached Excel file traces how the algorithm determines a root for a cubic equation.

26KB

Xsolve SECANT example.xlsx

Open