Solve functions (Xsolve)

Syntax: Xsolve_<parameter>

Solver What-If Analysis similar to Goal Seek with each API call to solve for a particular input value. For example, Xsolve can be used to determine the sum assured for a life insurance policy given a target policy premium.

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

Sample file

Xsolve.xlsx

23KB

Open

1. If you have Coherent Assistant installed, you can use the Solver feature to run and trace Xsolves within the workbook.

2. Upload the file to Spark and test in the API Tester.

3. Copy the structure into your workbook and tie it into the existing calculations.

4. Include any additional Xsolve parameters if required.

Xsolve 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 proper numbers.

  • The maximum iterations is reached.

• #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 request_meta debug_solve.

1. Open the API Tester for the service.

2. In API request (inputs), select Raw view.

3. In the request_meta, add a comma , to the end of the last entry and then add "debug_solve": true.

4. Submit the API call.

5. In API response (outputs), select Raw view. Under response_data, outputs, additional_outputs contains a solve_log array with a list of the solve iterations.

Sample solve_log

{
    "response_data": {
        "outputs": {
            ...,
            "additional_outputs": {
                "solve_log": [
                    "Solve BRENT Sheet1!F12:",
                    "target value: 0",
                    "initial guess: 10",
                    "max change: 0.001",
                    "max iterations: 50",
                    "bounds: [0, 10]",
                    "f(0.0000) = -3, calculated 2 cells, 0 ms, [0,10] = 10.0000 #1",
                    "f(10.0000) = 437, calculated 2 cells, 0 ms, [0,10] = 10.0000 #2",
                    "bounds: [0.0000, 10.0000]",
                    "f(0.0682) = -2.75484832832457, calculated 2 cells, 0 ms, [0,10] = 10.0000 #3",
                    "f(2.3020) = -13.3881286292643, calculated 2 cells, 0 ms, [0,10] = 9.9318 #4",
                    "f(3.3373) = -19.3066838282396, calculated 2 cells, 0 ms, [2,10] = 7.6980 #5",
                    "f(4.6497) = -13.5945885050783, calculated 2 cells, 0 ms, [3,10] = 6.6627 #6",
                    "f(4.9725) = -8.5153534330259, calculated 2 cells, 0 ms, [5,10] = 5.3503 #7",
                    "f(7.4863) = 110.241107313455, calculated 2 cells, 0 ms, [5,10] = 5.0275 #8",
                    "f(5.4653) = 2.88985459816482, calculated 2 cells, 0 ms, [5,7] = 2.5137 #9",
                    "f(5.3466) = -0.292242481169151, calculated 2 cells, 0 ms, [5,5] = 0.4928 #10",
                    "f(5.3684) = 0.270730410208451, calculated 2 cells, 0 ms, [5,5] = 0.1187 #11",
                    "f(5.3579) = 3.29746925125107e-05, calculated 2 cells, 0 ms, [5,5] = 0.0218 #12",
                    "f(5.3579) = -8.78586092767364e-11, calculated 2 cells, 0 ms, [5,5] = 0.0114 #13",
                    "f(5.3579) = 1.64872982040265e-05, calculated 2 cells, 0 ms, [5,5] = 0.0000 #14",
                    "Success: f(5.3579) = 0.0000",
                    "Iterations: 14",
                    "Solve Sheet1!F12 result: 5.35794803212251 f(5.35794803212251) = 1.64872982040265e-05, 1 ms"
                ]
            }
        },
        "warnings": null,
        "errors": null
    },
    "response_meta": {
        ...     
    }
}

BRENT algorithm

The implementation aligns with 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 algorithm

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