# Using a Circular Reference in a Formula

Learn how to use circular references in formulas and iterative calculations to approach an optimum value by iterating on the same function.

## Content

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](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulacircref#iterative-calculations-in-a-formula)
2. [Locate Circular References in a Formula](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulacircref#locate-circular-references-in-a-formula)
3. [Classifying Detected and Removed Circular References](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulacircref#Classifying-Detected-and-Removed-Circular-References)
4. [Getting All Circular References of Workbook and Worksheet](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulacircref#Getting-All-Circular-References-of-Workbook-and-Worksheet)

## 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](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.SheetView.MaximumIterations.html) property. You can set the amount of change allowed with the [MaximumChange](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.SheetView.MaximumChange.html) 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](/spreadnet/docs/latest/online-win/overview/spwin-designerguide/spwin-spd-design/spwin-spd-setformula). For details on the functions and operators that can be used to create a formula, refer to the [Formula Reference](https://developer.mescius.com/spreadnet/docs/latest/online-formula/overview.html).
**Using Code**

1. Set the cell types for the cells with the formulas.
2. Set the recalculation iteration count by setting the [MaximumIterations](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.SheetView.MaximumIterations.html) property for the sheet.
3. Specify the maximum amount of change that can occur with each iteration by setting the [MaximumChange](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.SheetView.MaximumChange.html) property for the sheet.
4. If needed, set the reference style for the sheet with the [ReferenceStyle](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.SheetView.ReferenceStyle.html) property.
5. Define the formulas with the circular reference(s) in the cells.

**Example**
This example sets formulas.

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

```vbnet
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](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.FpSpread.CircularFormula.html) 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.

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

```vbnet
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](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.FpSpread.CircularCellChanged.html) 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](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CircularCellChangedEventArgs.html) class play crucial roles in classifying circular references:

* [IncludedCircularReferences:](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CircularCellChangedEventArgs.IncludedCircularReferences.html) This property includes all newly detected circular references. It helps you identify cells that cause circular calculations.
* [ExcludedCircularReferences:](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.CircularCellChangedEventArgs.ExcludedCircularReferences.html) 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 ](/spreadnet/api/latest/online-win/FarPoint.Win.Spread/FarPoint.Win.Spread.FpSpread.CombineCircularCellChangedEvents.html)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**

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

**Exmaple**
This example classifies detected and removed circular references.

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

```vbnet
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](/spreadnet/api/latest/online-win/GrapeCity.Spreadsheet/GrapeCity.Spreadsheet.IWorkbook.CircularReferences.html) and [IWorksheet.CircularReferences](/spreadnet/api/latest/online-win/GrapeCity.Spreadsheet/GrapeCity.Spreadsheet.IWorksheet.CircularReferences.html) properties to get all circular references in a workbook or a specific worksheet.
**Using code**

1. Creating two sheets that both have circular references.
2. Use IWorkbook.CircularReferences to find all circular references in the entire workbook.
3. Use IWorksheet.CircularReferences to find all circular references in the specific worksheet.

**Exmaple**
This example retrieves all circular references in a workbook and the active worksheet.

```csharp
fpSpread1.Sheets.Count = 2;
var activeSheet = fpSpread1.AsWorkbook().ActiveSheet;
activeSheet.Cells[3, 3].Formula = "A1";
activeSheet.Cells[0, 0].Formula = "A2";
activeSheet.Cells[1, 0].Formula = "D4";
fpSpread1.AsWorkbook().Worksheets[1].Cells[4, 0].Formula = "sum(A1:A5)";
var workbook_circularReferences = fpSpread1.AsWorkbook().CircularReferences;
if (workbook_circularReferences != null)
{
    for (int i = 0; i < workbook_circularReferences.Areas.Count; i++) 
    {
        var cell = workbook_circularReferences.Areas[i];
        Console.WriteLine($"Circular reference in workbook at sheet [{cell.Worksheet.Name}] cell [{cell.Row}, {cell.Column}]");
    }
}
var activeSheet_circularReferences = fpSpread1.AsWorkbook().ActiveSheet.CircularReferences;
if (activeSheet_circularReferences != null)
{
    for (int i = 0; i < activeSheet_circularReferences.Areas.Count; i++)
    {
        var cell = activeSheet_circularReferences.Areas[i];
        Console.WriteLine($"Circular reference in worksheet at cell [{cell.Row}, {cell.Column}]");
    }
}
```

```vbnet
fpSpread1.Sheets.Count = 2
Dim activeSheet = fpSpread1.AsWorkbook().ActiveSheet
activeSheet.Cells(3, 3).Formula = "A1"
activeSheet.Cells(0, 0).Formula = "A2"
activeSheet.Cells(1, 0).Formula = "D4"
fpSpread1.AsWorkbook().Worksheets(1).Cells(4, 0).Formula = "sum(A1:A5)"
Dim workbookCircularReferences = fpSpread1.AsWorkbook().CircularReferences
If workbookCircularReferences IsNot Nothing Then
    For i As Integer = 0 To workbookCircularReferences.Areas.Count - 1
        Dim cell = workbookCircularReferences.Areas(i)
        Console.WriteLine($"Circular reference in workbook at sheet [{cell.Worksheet.Name}] cell [{cell.Row}, {cell.Column}]")
    Next
End If
Dim activeSheetCircularReferences = fpSpread1.AsWorkbook().ActiveSheet.CircularReferences
If activeSheetCircularReferences IsNot Nothing Then
    For i As Integer = 0 To activeSheetCircularReferences.Areas.Count - 1
        Dim cell = activeSheetCircularReferences.Areas(i)
        Console.WriteLine($"Circular reference in worksheet at cell [{cell.Row}, {cell.Column}]")
    Next
End If
```

## See Also

[Formulas in Cells](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula)
[Placing a Formula in Cells](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulaplace)
[Specifying a Cell Reference in a Formula](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulacellref)
[Specifying a Sheet Reference in a Formula](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulasheetref)
[Specifying an External Reference in a Formula](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-externalref)
[Nesting Functions in a Formula](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulanested)
[Recalculating and Updating Formulas Automatically](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formularecalc)
[Finding a Value Using GoalSeek](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formula-goalseek)
[Allowing the User to Enter Formulas](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulaallowuser)
[Creating and Using a Custom Name](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulacustomname)
[Creating and Using a Custom Function](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulacustomfunc)
[Creating and Using External Variable](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-external-variable)
[Using the Array Formula](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-arrayformula)
[Working with the Formula Text Box](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-formulabar)
[Setting up the Name Box](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-namebox)
[Using Language Package](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/UsingLanguagePackage)
[Accessing Data from Header or Footer](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-headerfooterformula)
[Managing External Reference](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/spwin-extrenalreference)
[Working With Dynamic Array Formulas](/spreadnet/docs/latest/online-win/overview/spwin-devguide/spwin-cell-formula/WorkingWithDynamicArrayFormulas)