Using a Circular Reference in a Formula

You can refer to a formula in the cell that contains that formula. This is a circular reference. This is done typically to recurse on a function to approach an optimum value by iterating on the same function. You can select how many times a function iterates on itself (recurses) by setting the recalculation iteration count property.

If the formula "=COLUMNS(A1:C5)" is in cell C4, no result is returned. In other words, if both the last column and row index of the array are greater than the column and row index of the cell in which the formula resides, the formula cannot be calculated. In this case, the cell C4 is in the range A1:C5. This a circular reference in a formula and so Spread does not evaluate the formula unless iterations are turned on.

As with most spreadsheet products (including Excel and OpenOffice), Spread solves circular formulas via iterations. During each recalculation cycle, a specified number of iterations are performed. During each iteration, every circular formula is evaluated exactly once. The exact order in which the circular formulas are evaluated during a given iteration can not be assumed by the application. As with most spreadsheet products (including Excel and OpenOffice), circular formulas in Spread are intended to be used in scenarios where the iterations converge to the desired solution regardless of the order of evaluation with in a given iteration.

For information on using the Formula Editor to enter a formula at design time, refer to Entering a Formula in Spread Designer. For details on the functions and operators that can be used to create a formula, refer to the Formula Reference.

Using Code

Set the cell types for the cells with the formulas.
Set the recalculation iteration count with the MaximumIterations property for the sheet.
Set the reference style for the sheet.
Define the formulas with the circular reference(s) in the cells.

Example

C#	Copy Code
fpSpread1.ActiveSheet.Iteration = true; fpSpread1.ActiveSheet.SetValue(0, 1, 20); fpSpread1.ActiveSheet.MaximumChange = 5; fpSpread1.ActiveSheet.MaximumIterations = 5; fpSpread1.ActiveSheet.SetFormula(0, 2, "A1*3"); fpSpread1.ActiveSheet.SetFormula(0, 0, "B1+C1");

Copy Code

fpSpread1.ActiveSheet.Iteration = true;
fpSpread1.ActiveSheet.SetValue(0, 1, 20);
fpSpread1.ActiveSheet.MaximumChange = 5;
fpSpread1.ActiveSheet.MaximumIterations = 5;
fpSpread1.ActiveSheet.SetFormula(0, 2, "A1*3");
fpSpread1.ActiveSheet.SetFormula(0, 0, "B1+C1");

VB	Copy Code
FpSpread1.ActiveSheet.Iteration = True FpSpread1.ActiveSheet.SetValue(0, 1, 20) FpSpread1.ActiveSheet.MaximumChange = 5 FpSpread1.ActiveSheet.MaximumIterations = 5 FpSpread1.ActiveSheet.SetFormula(0, 0, "B1+C1") FpSpread1.ActiveSheet.SetFormula(0, 2, "A1*3")

Copy Code

FpSpread1.ActiveSheet.Iteration = True
FpSpread1.ActiveSheet.SetValue(0, 1, 20)
FpSpread1.ActiveSheet.MaximumChange = 5
FpSpread1.ActiveSheet.MaximumIterations = 5
FpSpread1.ActiveSheet.SetFormula(0, 0, "B1+C1")
FpSpread1.ActiveSheet.SetFormula(0, 2, "A1*3")