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.
This topic explains the following tasks:
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 is 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.
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.
This example sets formulas.
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"); |
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") |
In Spread for Winforms, you can use the CircularFormula event to detect circular references and eliminate them in order to avoid calculation errors in the formulas used in spreadsheets.
This example detects circular references in a formula.
C# |
Copy Code
|
---|---|
fpSpread1.CircularFormula += delegate (object sender1, CircularFormulaEventArgs e1) { for (int i = 0; i < e1.CircularCells.Count; i++) { Console.WriteLine("Circular formula detected at cell [{0:d}, {0:d}]!", e1.CircularCells[i].Row, e1.CircularCells[i].Column); } }; fpSpread1.ActiveSheet.Cells[3, 3].Formula = "A1"; fpSpread1.ActiveSheet.Cells[0, 0].Formula = "A2"; fpSpread1.ActiveSheet.Cells[1, 0].Formula = "D4"; |
VB |
Copy Code
|
---|---|
fpSpread1.CircularFormula += Sub(sender1 As Object, e1 As CircularFormulaEventArgs) For i As Integer = 0 To e1.CircularCells.Count - 1 Debug.WriteLine("Circular formula detected at cell [{0:d}, {0:d}]!", e1.CircularCells(i).Row, e1.CircularCells(i).Column) Next fpSpread1.ActiveSheet.Cells(3, 3).Formula = "A1" fpSpread1.ActiveSheet.Cells(0, 0).Formula = "A2" fpSpread1.ActiveSheet.Cells(1, 0).Formula = "D4" |