Spread Windows Forms 17
Spread Windows Forms 17.0 Product Documentation / Developer's Guide / Formulas in Cells / Using a Circular Reference in a Formula
In This Topic
    Using a Circular Reference in a Formula
    In This Topic

    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.

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

    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.

    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"
    
    See Also