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 using 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	TRUE or FALSE value. Determines whether or not this solve should be executed.
Target cell	Link to the formulated cell that needs to reach the Target value. This is recommended to be defined as a difference between the desired Target value of 0. This must be a link to a cell on the same worksheet or a Named Range. Equivalent to Goal Seek Set cell.
Target value	Value of the Target cell to achieve. This is best set to 0. Equivalent to Goal Seek Target to value.
By changing	Link to the cell that has to change in order to for the Target cell to reach the Target value. This must be a link to a cell on the same worksheet or a Named Range. Equivalent to Goal Seek By changing cell.
Solve algorithm	SECANT or BRENT. We recommend using BRENT as it is able to solve more complex workbooks accurately.
Max change	Maximum acceptable value of |Solve result(i) - Solve result(i-1)| within Max iterations, default 1.
Max iterations	Maximum iterations for the number of solve, default 25.
Initial guess	Initial guess to help reach the Target value sooner.
Lower bound	For the BRENT algorithm, 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 BRENT algorithm, 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	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	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.

Xsolve.xlsx

23KB

Open

Solve algorithm reference

BRENT

The implementation aligns with the Brent's method.

• Bound the independent variable between 0 and Initial guess * 100. This can be overridden using the Lower bound and Upper bound parameters.

• Convergence condition |Solve result(i) - Solve result(i-1)| .

SECANT

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

• The solve uses Initial guess and Initial guess * 1.5 as the starting points.

• Convergence condition |Solve result(i) - Solve result(i-1)| .

Xsolve SECANT example.xlsx

28KB

Open