Formulas and calculated columns

A calculated column is a column whose value is a formula over the row's other columns — the way a spreadsheet user would write it:

import { DataGrid } from '@kanunilabs/datagrid-react';

<DataGrid
  dataSource={orders}
  rowKey="id"
  columns={[
    { field: 'product', headerName: 'Product' },
    { field: 'price', headerName: 'Unit price', dataType: 'number' },
    { field: 'qty', headerName: 'Qty', dataType: 'number' },
    { id: 'total', headerName: 'Total', formula: '[Unit price] * [Qty]' },
    { id: 'level', headerName: 'Stock', formula: 'IF([Qty] < 10; "Low"; "OK")' },
  ]}
/>

The column behaves like data: it sorts, filters, searches, groups, takes a footer total, exports and is part of the saved view. It is not editable — there is nothing to write the value back to. A formula is Community.

The formula runs without eval or new Function, so it works on pages with a strict Content-Security-Policy.

Writing a formula

• Columns are written in brackets: [qty] (the column id) or [Unit price] (the header text), case-insensitively. A ] inside a name is written ]].
• Operators, in Excel's order: comparison = <> < <= > >=, then & (join text), + -, * /, ^, unary -, and a trailing %. -2^2 is 4, as in Excel.
• Arguments are separated by ; or , — ROUND([Price]; 2) and ROUND([Price], 2) are the same.
• Text is in double quotes; "" is a quote inside it. TRUE and FALSE are booleans.
• Blank counts as 0 in arithmetic and "" in text.
• Dates count in days: a date plus 7 is a week later, and a date minus a date is the days between them. An ISO day string such as "2026-03-15" — what a JSON API sends — counts as a date.

ROUND rounds half away from zero (ROUND(1.005; 2) is 1.01), and UPPER / LOWER follow the grid's locale — in Turkish, i becomes İ.

A calculated column can read another. Its dataType defaults to what the formula produces (arithmetic is a number, & is text, a comparison is a boolean), so it sorts and aligns correctly without one.

When a formula goes wrong

Errors are values, as in a spreadsheet, and a cell shows the code:

Errors propagate through operators, IFERROR(x; fallback) catches them, and they sort with the blanks. For a #NAME? column, controller.getFormulaError(id) says why and where: { message, position, kind, params }.

Letting users add columns

Enterprise. With calculatedColumns, the column menu offers Add calculated column…; on a column the user added, also Edit and Remove.

<EnterpriseDataGrid calculatedColumns dataSource={orders} columns={columns} />

The dialog takes a name and a formula, inserts columns and functions at the caret, previews the first rows as you type, and explains an error in the grid's language with the character it is at — Save stays disabled until the formula compiles. The new column is placed after the one whose menu it came from.

It is off by default, so an upgrade does not put a new entry in front of every user of every grid. The same from code:

const id = controller.addCalculatedColumn({ headerName: 'Margin', formula: '[Revenue] - [Cost]', after: 'cost' });
controller.updateCalculatedColumn(id, { formula: 'ROUND([Revenue] - [Cost]; 0)' });
controller.removeCalculatedColumn(id); // also drops its sort, filter or grouping
controller.previewFormula('[Revenue] / [Units]'); // { ok, values, dataType } or { ok: false, error }

Columns added this way are kept in the saved view (calculatedColumns), and they stay when the app passes its columns again. A Community grid does not restore them.

Cell formulas

Enterprise, with editing. On a column with allowFormulas, a cell can hold a formula: the user types = and the rest, as in a spreadsheet.

<EnterpriseDataGrid
  editing={{ mode: 'cell' }}
  columns={[
    { field: 'qty', headerName: 'Qty', dataType: 'number' },
    { field: 'price', headerName: 'Price', dataType: 'number' },
    { field: 'total', headerName: 'Total', dataType: 'number', allowFormulas: true },
  ]}
/>

The data keeps the formula text; the grid shows, sorts, filters, totals and exports the result. The editor of such a column is a text box whatever its dataType — a number box would not take = — and a plain number typed into a number column is still stored as a number.

• [Qty] is this row's Qty, as in a calculated column. Copy the formula down and every row reads its own.
• A1, B2:B9 are the cells on screen: the letter is the column's visible position, the number the row's position among the data rows. Typed references are stored by column id and row key — ={qty@17}*2 — and shown back as A1 at wherever that cell is now, so sorting or filtering afterwards never repoints a formula. A range is kept a range while those cells are adjacent in the data; one framed over a sorted screen is stored as the cells the user framed.
• $ fixes a part, as in Excel: $A$1 stays put when the fill handle copies the formula; relative references move with it, along the screen.
• A formula that reads another row updates when that row is edited. One that reads itself shows #CYCLE!; a reference to a row that no longer exists shows #REF!.

A formula that arrives with the data — written by your backend — may use the stored {column@key} form, or plain A1, which then counts in data order: the order of the rows you passed and of the column definitions.

Cell formulas are for data in the browser (an array). controller.getCellFormula(rowKey, columnId) returns a cell's formula text, or null for a plain value.

In Excel

Enterprise. The Excel export writes calculated columns and cell formulas as Excel formulas over the sheet's cells, with the grid's result cached — details in Excel: formulas and links. Excel then recalculates with its own rules, which differ at the edges: text that merely looks like a number or a date is not coerced the same way everywhere.

What it does not do yet

• Function names are English (IF, not EĞER).
• A pasted formula is stored as typed: its A1 references then count in data order rather than on the screen it was pasted onto.