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 recursively perform 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. You can also set the maximum amount of change. If the amount of change (difference between the current and previous formula result) is greater than the maximum change value, the formula continues until it reaches the maxium number of iterations or the formula result change is less than the maximum change value.

By default, 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.

For more information about formulas, refer to the Formula Reference.

Return to the overview of formulas at Managing Formulas.

Using Code

Set the cell types for the formula.
Set the recalculation iteration count with the MaximumIterations property for the sheet.
Set the reference style for the sheet.
Use the circular reference in a formula in a cell.

Example

This example uses a circular reference in a cell and sets the iterations.

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

Copy Code

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

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

Copy Code

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

Using the Spread Designer

Select the Settings menu.
Select the Calculation icon under the Sheet Settings section.
Check the Iteration check box.
Set Maximum Change and Maximum Iterations.
Select OK to close the dialog.
Click Apply and Exit to close the Spread Designer.