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:

1. Iterative Calculations in a Formula
2. Locate Circular References in a Formula

Iterative Calculations in a Formula

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.

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

Locate Circular References in a Formula

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.

Using Code

1. Create a new Circular Formula event.
2. Run a for loop to find all circular references in the spreadsheet.
3. Eliminate circular references from the spreadsheet.

Example

This example detects circular references in a formula.

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

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"