Spread Windows Forms Product Documentation

Using a Circular Reference in a Formula

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:

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

Set the cell types for the cells with the formulas.
Set the recalculation iteration count by setting the MaximumIterations property for the sheet.
Specify the maximum amount of change that can occur with each iteration by setting the MaximumChange property for the sheet.
If needed, set the reference style for the sheet with the ReferenceStyle property.
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

Create a new Circular Formula event.
Run a for loop to find all circular references in the spreadsheet.
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"

Classifying Detected and Removed Circular References

Spread for WinForms supports CircularCellChanged event to monitor changes of circular references in spreadsheets. This event is triggered when creating, editing, removing the circular references, helping you to deal with situations that may affect formula calculations.

Two properties of the CircularCellChangedEventArgs class play crucial roles in classifying circular references:

IncludedCircularReferences: This property includes all newly detected circular references. It helps you identify cells that cause circular calculations.
ExcludedCircularReferences: This property includes removed circular references, providing a list of resolved circular references.

The CircularCellChanged event is triggered once for each cell change in a circular reference. However, by setting FpSpread.CombineCircularCellChangedEvents to true, you can reduce the frequency of event triggering. This is achieved by combining all changes into one trigger, which streamlines the process of handling circular reference changes.

Using code

Set the property CombineCircularCellChangedEvents to true.
Create a new CircularCellChanged event.
Use CircularCellChangedEventArgs.IncludedCircularReferences to find all newly circular references in the spreadsheet.
Use CircularCellChangedEventArgs.ExcludedCircularReferences to find all removed circular references in the spreadsheet.

Exmaple

This example classifies detected and removed circular references.

fpSpread1.CombineCircularCellChangedEvents = true;
fpSpread1.CircularCellChanged += (sender, e) =>
{
    if (e.IncludedCircularReferences != null)
    {
        foreach (var reference in e.IncludedCircularReferences)
        {
            Console.WriteLine($"Detected circular reference at cell {reference.ToString()}");
        }
    }
    if (e.ExcludedCircularReferences != null)
    {
        foreach (var reference in e.ExcludedCircularReferences)
        {
            Console.WriteLine($"Removed circular reference from cell {reference.ToString()}");
        }
    }
};  
var activeSheet = fpSpread1.AsWorkbook().ActiveSheet;
activeSheet.Cells[3, 3].Formula = "A1";
activeSheet.Cells[0, 0].Formula = "A2";
activeSheet.Cells[1, 0].Formula = "D4";

fpSpread1.CombineCircularCellChangedEvents = True
AddHandler fpSpread1.CircularCellChanged, Sub(sender, e)
    If e.IncludedCircularReferences IsNot Nothing Then
        For Each reference In e.IncludedCircularReferences
            Console.WriteLine($"Detected circular reference at cell {reference.ToString()}")
        Next
    End If
    If e.ExcludedCircularReferences IsNot Nothing Then
        For Each reference In e.ExcludedCircularReferences
            Console.WriteLine($"Removed circular reference from cell {reference.ToString()}")
        Next
    End If
End Sub
Dim activeSheet = fpSpread1.AsWorkbook().ActiveSheet
activeSheet.Cells(3, 3).Formula = "A1"
activeSheet.Cells(0, 0).Formula = "A2"
activeSheet.Cells(1, 0).Formula = "D4"

Getting All Circular References of Workbook and Worksheet

Spread for WinForms provides IWorkbook.CircularReferences and IWorksheet.CircularReferences properties to get all circular references in a workbook or a specific worksheet.