Spread for WPF supports circular references in formulas, which occur when a formula directly or indirectly references its own cell. Circular references are often used to perform iterative calculations to converge on an optimal value.

To enable iterative calculations for circular references in a workbook, you can set the Iterative property of the CalculationEngine class to true. Additionally, you can also use the MaximumIterations property to specify the maximum number of iterations for the calculation and the MaximumChange property to define the maximum allowable change between iterations.

Here is an example of how to create a circular reference using cell A1. The formula “=A1*3” is applied to cell C1, while the formula “=B1+C1” is assigned to cell A1. In this scenario, cell A1 calculates the sum of the values in cells B1 and C1, and cell C1 references A1 and returns the result back to A1. This configuration creates a circular reference as soon as A1's value is calculated. Additionally, the maximum number of calculation iterations is set to 5, allowing the result to be recalculated multiple times to achieve an optimal value.

// Circular Reference.
spreadSheet1.Workbook.WorkbookSet.CalculationEngine.Iterative = true;
spreadSheet1.Workbook.ActiveSheet.Cells[0, 1].Value = 20;
spreadSheet1.Workbook.WorkbookSet.CalculationEngine.MaximumChange = 5;
spreadSheet1.Workbook.WorkbookSet.CalculationEngine.MaximumIterations = 5;
spreadSheet1.Workbook.ActiveSheet.Cells[0, 2].Formula = "=A1*3";
spreadSheet1.Workbook.ActiveSheet.Cells[0, 0].Formula = "=B1+C1";
spreadSheet1.Workbook.WorkbookSet.CalculationEngine.Calculate();
spreadSheet1.Workbook.WorkbookSet.CalculationEngine.TriggerVolatileCells();
spreadSheet1.Workbook.ActiveSheet.CircularReference.Activate();
Console.WriteLine("Precedents: " + spreadSheet1.Workbook.ActiveSheet.Cells["A1"].Precedents.ToString());
Console.WriteLine("DirectPrecedents: " + spreadSheet1.Workbook.ActiveSheet.Cells["A1"].DirectPrecedents.ToString());
Console.WriteLine("Dependents: " + spreadSheet1.Workbook.ActiveSheet.Cells["A1"].Dependents.ToString());
Console.WriteLine("DirectDependents: " + spreadSheet1.Workbook.ActiveSheet.Cells["A1"].DirectDependents.ToString());

' Circular Reference.
spreadSheet1.Workbook.WorkbookSet.CalculationEngine.Iterative = True
spreadSheet1.Workbook.ActiveSheet.Cells(0, 1).Value = 20
spreadSheet1.Workbook.WorkbookSet.CalculationEngine.MaximumChange = 5
spreadSheet1.Workbook.WorkbookSet.CalculationEngine.MaximumIterations = 5
spreadSheet1.Workbook.ActiveSheet.Cells(0, 2).Formula = "=A1*3"
spreadSheet1.Workbook.ActiveSheet.Cells(0, 0).Formula = "=B1+C1"
spreadSheet1.Workbook.WorkbookSet.CalculationEngine.Calculate()
spreadSheet1.Workbook.WorkbookSet.CalculationEngine.TriggerVolatileCells()
spreadSheet1.Workbook.ActiveSheet.CircularReference.Activate()
Console.WriteLine("Precedents: " & spreadSheet1.Workbook.ActiveSheet.Cells("A1").Precedents.ToString().ToString())
Console.WriteLine("DirectPrecedents: " & spreadSheet1.Workbook.ActiveSheet.Cells("A1").DirectPrecedents.ToString().ToString())
Console.WriteLine("Dependents: " & spreadSheet1.Workbook.ActiveSheet.Cells("A1").Dependents.ToString().ToString())
Console.WriteLine("DirectDependents: " & spreadSheet1.Workbook.ActiveSheet.Cells("A1").DirectDependents.ToString().ToString())