Calculate circular references (Xcircular_switch)

Spark supports circular references - with just a few steps, you can enable a circular reference in your Excel file to run within your Spark Service.

File requirements

Circular Switch

Spark requires files to have a "circular switch" included within your circular reference to enable Spark to properly trigger the calculation.

A "circular switch" acts as toggle that can turn the circular reference on or off. This helps stabilize the circular reference in Excel, and allows Spark to similarly activate the circular reference upon calculation. You can find an example of a circular switch below. Circular wwitches should be set up as Boolean values, and are recommended to be set to a TRUE value upon compile.

Setup a Circular Switch

Setting up a circular switch can be done in a few short steps:

1. Using the name manager, name a blank cell with the named ranger XCircular_Switch and set the value to TRUE.

2. Next, find your circular reference, and select any cell within the loop, and update your formula to only run the circular reference when your circular switch is equal to TRUE.

Iterative calculation settings

1. Spark can match the iterative calculation settings in your workbook upon compile.

2. You can check your iterative calculation settings in Excel via: File -> Options -> Formulas.

   1. Note: To enable circular references in Excel, the Enable iterative calculation setting must be checked.

   2. By default, Excel will set Maximum Iterations to 100, and Maximum Change to 0.001.

   3. Note: Spark will only match iterations to a max of 250. If maximum iterations in the workbook are above 250, they will be defaulted to 250 upon upload. If Enable iterative calculation is not checked at time of upload, then Spark will default Maximum Iterations to 100 and Maximum Change to 0.001 in the Spark service.

Upload your file to Spark

Upon uploading your file to Spark, the compiler will detect your circular switch and circular references, and notify you that there is a circular reference in your workbook. It will also provide information on the maximum iterations and maximum change for the circular reference in your compiled service.

INFO: Cell Sheet1!B2: INFO: Circular references detected in workbook. Spark will process the circular references with the following settings: max iterations 100, max change 0.001.

Example of XCircular_Switch mapping

This service can be uploaded to Spark to use as a simple example.

11KB

Circular Reference Example.xlsx

Open