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:
28.png
Value of formula will be calculated and displayed when formula has been changed.
29.png
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.
13.png
Sample of reference from another cell:
94.png
Result:
95.png

References of Range

The format StartCell:EndCell used to describe an address to reference a range:
91.png
Result:
92.png

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.
83.png
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.
93.png

Next: Script Execution

Last edited Apr 21, 2014 at 3:14 PM by unvell, version 23