# Formula

**NOTE: All functions used in formula which is designed to be compatible with Excel will changed name to uppercase, such as
SUM and AVERAGE, this change is applied from v0.8.6.**
Data of cell start with '=' will be processed as formula automatically.

Cells with formula will be updated automatically when data of referenced cells have been changed.

Input a formula:

Value of formula will be calculated and displayed when formula has been changed.

Data of cell start with (') will be ignored as formula.

## References of Cell

It is same as Excel, reference to a Cell or Range are also supported by ReoGrid.

Sample of reference from another cell:

Result:

## References of Range

The format

*StartCell:EndCell* used to describe an address to reference a range:

Result:

## Get reference list of formula by programming

**This feature to be available in 0.8.6**
grid["H8"] = "=A1+B1-SUM(A1:C3)+AVERAGE(D1:H5)";
// get reference list from cell
var rangeList = grid.GetCellFormulaReferenceRanges("H8");
// get information from reference list
AssertTrue(rangeList != null);
AssertEquals(rangeList[2].Range, new ReoGridRange("A1"));
AssertEquals(rangeList[3].Range, new ReoGridRange("B1"));
AssertEquals(rangeList[0].Range, new ReoGridRange("A1:C3"));
AssertEquals(rangeList[1].Range, new ReoGridRange("D1:H5"));

## Supported built-in functions

For details refer to

List of Excel-compatible functions.

**Note**: ReoGrid formula language is case sensitive, all functions to be compatible with Excel must be named in uppercase, like

SUM and

AVERAGE, call function by using names like

sum will cause a 'function not found' exception.

## Trace Precedents

A feature to trace formula precedents for cells like in Excel is available from v0.8.6.

To enable trace precedents, set

TraceFormulaPrecedent property of cell to true:

grid["C2"] = 10;
grid["C3"] = 5;
ReoGridCell cell = grid.Cells["C5"];
cell.Formula = "=C2+C3";
cell.TraceFormulaPrecedents = true;

or by call

TraceCellPrecedents of control:

grid.TraceCellPrecedents("C5");

Once formula changed, the trace arrows will disappear. To always enable trace formula use the following code:

grid.CellDataChanged += (s, e) => e.Cell.TraceFormulaPrecedents = true;

## Trace Dependents

Usage refer to Trace Precedents.

## Reference Named Range

From v0.8.6, definition and reference from a named range in formula is available.

// define a named range
NamedRange namedRange = grid.DefineNamedRange("items", "B2:E2");
// set values into named range
namedRange.Data = new object[] { 1, 3, 5, 7 };
// set formula sum from a named range
grid["F2"] = "=SUM(items)";

Run the code above behind ReoGridEditor, run and show the editor form, and select the range of items, the name of range 'items' will show in the address bar on the left top of editor form.