You can refer to a formula in the cell that contains that formula; this type of reference is called 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 using the MaximumIterations property. You can set the amount of change allowed with the MaximumChange property.

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.

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 cannot 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 within 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

1. Set the cell types for the cells with the formulas.
2. Set the recalculation iteration count by setting the MaximumIterations property for the sheet.
3. Specify the maximum amount of change that can occur with each iteration by setting the MaximumChange property for the sheet.
4. If needed, set the reference style for the sheet with the ReferenceStyle property.
5. Define the formulas with the circular reference(s) in the cells.

Example

This example sets formulas.

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

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

Copyright © GrapeCity, inc. All rights reserved.