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. You can select how many times a function iterates on itself (recurses) by setting the MaximumIterations 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 maximum 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.
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"); 
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") 