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.

Using Code

1. Set the Iteration property to true to calculate the circular reference.
2. Set the cell types for the formula.
3. Set the recalculation iteration count with the MaximumIterations property for the sheet.
4. Set the reference style for the sheet.
5. Use the circular reference in a formula in a cell.

Example

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

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"); 

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

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

Copyright © GrapeCity, inc. All rights reserved.