> For the complete documentation index, see [llms.txt](https://docs.coherent.global/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.coherent.global/build-spark-services/additional-mappings/solve-functions-xsolve.md). # Solve functions (Xsolve) Syntax: `Xsolve_` Solver **What-If Analysis** similar to [Goal Seek](https://support.microsoft.com/en-US/Excel/use-goal-seek-to-find-the-result-you-want-by-adjusting-an-input-value) 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 {% file src="/files/92JgsoxYmfdUmELtyHpj" %} 1. If you have [Coherent Assistant](https://docs.coherent.global/assistant/) installed, you can use the [Solver](/assistant/solver/creating-a-solver.md) feature to run and trace `Xsolve`s within the workbook. 2. Upload the file to Spark and test in the [API Tester](/navigation/api-tester.md). 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 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)](/spark-apis/execute-api/execute-api-v3.md#request_meta) `debug_solve`. 1. Open the [API Tester](/navigation/api-tester.md) for the service. 2. In **API request (inputs)**, select **Raw view**. 3. In the `request_meta`, add a comma `,` to 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`, there is an array called `solve_log` with a list of the solve iterations.
Sample solve_log ```json { "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](https://en.wikipedia.org/wiki/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](https://en.wikipedia.org/wiki/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)|` . {% file src="/files/soAx5yfG4wWqyCXPmpJr" %}