React Data GridFormulas

Formula strings can be provided to cells in the grid, allowing for dynamic calculations based on other cell values.

Enabling Formulas Copy Link

To enable formulas, set the gridOption property enableFormulas to true, and ensure that your rows have Row IDs.

When using formulas, the grid enables the following features by default:

• Range Selection and the Fill Handle
• Row Numbers

Certain features do not work in conjunction with formulas:

• Tree Data & Row grouping
• Pivoting & Aggregation
• Master Detail
• Row Sorting and Filtering
• Cell data type detection is no longer enabled by default.

Formula Syntax Copy Link

Formulas are a text string that starts with an equals sign (=) and can contain references to other cells, functions, operators, and constants.

Examples of valid formula strings:

• Constants (e.g. =3.14 or ="Hello")
• Cell references (e.g. =A1)
• Mathematical operations (e.g. =A1 + 3)
• Functions (e.g. =SUM(A1, 2))
• Cell Ranges (e.g. =SUM(A1:A10))

Constants Copy Link

Constants can be numbers (e.g. 3.14, 42, -7), strings (e.g. "Hello", "World"), or boolean values (TRUE, FALSE).

Cell references Copy Link

Cell references are used to refer to the value of another cell in the grid and are symbolised by an alphabetical column identifier followed by a numerical row identifier. After the 26th column, the columns continue with two letters (e.g. AA, AB, AC, etc.). Rows are numbered starting from 1.

Formulas can reference other cells using their column letter and row number. Columns are labeled alphabetically (A, B, C, ..., Z, AA, AB, etc.), and rows are numbered starting from 1.

Examples of cell references:

• =A1 refers to the cell in column A, row 1.
• =B2 refers to the cell in column B, row 2.

If a cell moves, the formula will always refer to the same cell, not the same position. For example, if a formula in cell C1 is =A1, and a new row is inserted above row 1, the formula will automatically update to =A2 to continue referencing the original cell. To instead always refer to the same position, use absolute references by prefixing the column letter and/or row number with a dollar sign ($):

• =$A$1 always refers to column A, row 1.
• =A$1 always refers to row 1, but the column can change.
• =$A1 always refers to column A, but the row can change.

When saving cell references, the grid converts these into a long hand format using column and row IDs to ensure that changes in the source data allow the grid to continue to refer to the correct cells when the data changes without the grid being open. For example, if the column with ID athlete is in column A, and the row with ID a is in row 1, then the formula =A1 becomes =REF(COL('athlete'), ROW('a')). When this is a static reference, for example $A$1, the long hand format uses A and 1 in place of IDs, but adds a true to indicate the value is absolute (e.g =$A$1 becomes =REF(COL('a', true), ROW('1', true))).

This long hand format can be used directly in your data source or application via a valueGetter, and will be converted to the short hand format when the user opens a cell editor.

Mathematical Operations Copy Link

Formulas can be used to construct a Mathematical expression using cell references, constants, functions, and operators and respect the standard order of operations (PEMDAS/BODMAS).

The following Mathematical operations are supported in formulas:

Functions Copy Link

Formulas can use functions to perform calculations on values. Functions are a string of letters followed by parentheses containing the function arguments. Functions can take multiple arguments, which can be constants, cell references, cell ranges, or other functions.

Examples of functions:

• =SUM(A1, 2) returns the sum of the value in cell A1 and the constant value 2.
• =AVG(A1:A10) returns the average of every cell value in the range A1 to A10. (e.g A1, A2, A3, ..., A10)
• =CONCAT(A1, " ", B1) returns the concatenation of the value in cell A1, a space, and the value in cell B1.
• =IF(A1 > 10, "High", "Low") returns "High" if the value in cell A1 is greater than 10, otherwise returns "Low".
• =SUM(AVG(A1:B1), 2) returns the sum of the average of the values in the range A1 to B1 and the constant value 2.
• =TODAY() returns a date object representing the current date.

The following functions are built into the grid by default:

Numeric:

Date:

Text:

Logical:

Counting:

Cell Ranges Copy Link

Cell ranges are used to refer to a rectangular block of cells in the grid and are symbolised by the top-left cell reference followed by a colon (:) and the bottom-right cell reference. For example, the range A1:B2 refers to the cells in column A and B, and rows 1 and 2 (i.e. A1, A2, B1, B2).

Custom Functions Copy Link

Custom functions can be provided to formulas by providing the gridOption property formulaFuncs.

The following snippet demonstrates how to write a custom function CUSTOMSUM which adds all of the provided values.

It uses the params.values iterator to simplify navigating all of the values, including those provided in ranges by flattening them into one iterable.

A user could then use this function in a formula such as:

• =CUSTOMSUM(A1:B2, 2) which would add all of the values in the range A1 to B2, and the constant 2.
• =CUSTOMSUM(1, 2, 3, 4) which would add the constant values 1, 2, 3, and 4.

In this snippet below, observe how the function uses the params.values iterator for navigating the values provided to the function, which provides all of the values from the ranges and constants in one simple iterable, meaning any input such as =CUSTOMSUM(A1:B2, 2) will be streamed as 1 (A1) -> 1 (A2) -> 1 (B1) -> 1 (B2) -> 2 for the function to process.

const enableFormulas = true;
const formulaFuncs = {
    CUSTOMSUM: {
        func: (params) => {
            let total = 0;
            for (const value of params.values) {
                total += value;
            }
            return total;
        },
    },
};

<AgGridReact
    enableFormulas={enableFormulas}
    formulaFuncs={formulaFuncs}
/>

Handling Errors Copy Link

It is the responsibility of the function implementation to throw errors when the arguments are not as expected.

The example below demonstrates a custom function ERRORIFONE which errors when any of the arguments provided are 1.

The following snippet builds on our ADD function to throw an error if any of the provided arguments are not numbers, or when no arguments are provided.

const enableFormulas = true;
const formulaFuncs = {
    ERRORIFONE: {
        func: (params) => {
            for (const value of params.values) {
                if (String(value) === '1') {
                    throw 'Error, discovered a \'1\' in params';
                }
            }
            return 'SUCCESS, no \'1\' found.';
        },
    },
};

<AgGridReact
    enableFormulas={enableFormulas}
    formulaFuncs={formulaFuncs}
/>

Complex Custom Functions Copy Link

Some custom functions may need explicit context about the ranges provided to them. For example, the COUNTIF function needs the first argument to be a range, and the second argument to be a criteria to apply to that range. If the params.values iterator was used, the range values would be flattened and hard to distinguish from the criteria.

The grid provides an alternative iterator params.args which provides wrapped arguments as they were provided to the function, without any flattening or transformation. This means that ranges are provided as a RangeParam object, which is iterable for navigating the values in the range.

The following example demonstrates how to implement a custom function COUNTEQ which counts the number of values in a range that match the second parameter.

The following snippet demonstrates providing a custom function COUNTEQ which counts the number of values in a range that match the second parameter.

const enableFormulas = true;
const formulaFuncs = {
    COUNTEQ: {
        func: (params) => {
            const argsArr = Array.from(params.args);
            if (argsArr.length != 2) {
                throw 'COUNTEQ requires exactly 2 arguments';
            }
            const [range, criteria] = argsArr;
            if (range.kind !== 'range') {
                throw 'First argument to COUNTEQ must be a range';
            }
            if (criteria.kind !== 'value' || typeof criteria.value === 'object') {
                throw 'Second argument to COUNTEQ must be a primitive value';
            }
            const isNumCriteria = typeof criteria.value === 'number';
            let count = 0;
            for (const value of range) {
                const coercedValue = isNumCriteria ? Number(value) : value;
                if (coercedValue === criteria.value) {
                    count++;
                }
            }
            return count;
        },
    },
};

<AgGridReact
    enableFormulas={enableFormulas}
    formulaFuncs={formulaFuncs}
/>