# Using a Circular Reference in a Formula

Spread for ASP.NET allows you to use circular references in a formula to recursively perform a function to approach an optimum value.

## Content

You can refer to a formula in the cell that contains that formula. This is a circular reference. This is done typically to recursively perform a function to approach an optimum value. You can select how many times a function iterates on itself (recurses) by setting the [MaximumIterations](/spreadnet/api/latest/online-asp/FarPoint.Web.Spread/FarPoint.Web.Spread.SheetView.MaximumIterations.html) property. You can also set the maximum amount of change. If the amount of change (difference between the current and previous formula result) is greater than the maximum change value, the formula continues until it reaches the maximum number of iterations or the formula result change is less than the maximum change value.
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 a circular reference in a formula and so Spread does not evaluate the formula unless iterations are turned on.
For more information about formulas, refer to the [Formula Reference](https://developer.mescius.com/spreadnet/docs/latest/online-formula/overview.html).

## Using Code

1. Set the [Iteration](/spreadnet/api/latest/online-asp/FarPoint.Web.Spread/FarPoint.Web.Spread.SheetView.Iteration.html) property to true to calculate the circular reference.
2. Set the cell types for the formula.
3. Set the recalculation iteration count with the [MaximumIterations](/spreadnet/api/latest/online-asp/FarPoint.Web.Spread/FarPoint.Web.Spread.SheetView.MaximumIterations.html) property for the sheet.
4. Set the reference style for the sheet.
5. Use the circular reference in a formula in a cell.

## Example

This example uses a circular reference in a cell and sets the iterations.

```csharp
FpSpread1.ActiveSheetView.Iteration = true;
FpSpread1.ActiveSheetView.SetValue(0, 1, 20);
FpSpread1.ActiveSheetView.MaximumChange = 5;
FpSpread1.ActiveSheetView.MaximumIterations = 5;
FpSpread1.ActiveSheetView.SetFormula(0, 0, "B1+C1");
FpSpread1.ActiveSheetView.SetFormula(0, 2, "A1*3");
```

```vbnet
FpSpread1.ActiveSheetView.Iteration = True
FpSpread1.ActiveSheetView.SetValue(0, 1, 20)
FpSpread1.ActiveSheetView.MaximumChange = 5
FpSpread1.ActiveSheetView.MaximumIterations = 5
FpSpread1.ActiveSheetView.SetFormula(0, 0, "B1+C1")
FpSpread1.ActiveSheetView.SetFormula(0, 2, "A1*3")
```

## Using the Spread Designer

1. Select the **Settings** menu.
2. Select the **Calculation** icon under the **Sheet Settings** section.
3. Check the **Iteration** check box.
4. Set **Maximum Change** and **Maximum Iterations**.
5. Select **OK** to close the dialog.
6. Click **Apply and Exit** to close the Spread Designer.

## See Also

[Iteration Property](/spreadnet/api/latest/online-asp/FarPoint.Web.Spread/FarPoint.Web.Spread.SheetView.Iteration.html)
[MaximumChange Property](/spreadnet/api/latest/online-asp/FarPoint.Web.Spread/FarPoint.Web.Spread.SheetView.MaximumChange.html)
[SetFormula Method](/spreadnet/api/latest/online-asp/FarPoint.Web.Spread/FarPoint.Web.Spread.SheetView.SetFormula.html)
[SetValue Method](/spreadnet/api/latest/online-asp/FarPoint.Web.Spread/FarPoint.Web.Spread.SheetView.SetValue.html)