#Formulas

The formula engine follows Excel: same operators and precedence, same coercion rules, same error values, 137 functions. It works on table structured references instead of A1 addresses, because grid rows move when you sort or filter.

#References

References use the column field (not the title). Unknown columns give #REF!, unknown names or functions give #NAME?.

#Language

Coercion: blank = 0 / "" / FALSE; numeric text works in arithmetic ("3"+1 = 4); dates are serial numbers (days since 1899-12-30), so [@due]-[@start] is a number of days and [@due]+30 is a date. ISO strings in cells are read as dates. Booleans are 1/0 in arithmetic.

Array math (Excel 365 style): operators work element by element on ranges, so these work:

⧉=SUM([qty]*[price])
=SUMPRODUCT(([status]="open")*[total])
=COUNT(FILTER([total],[city]="Recife"))
=MAX(([city]="Recife")*[total])

A cell cannot hold an array: a formula whose final result is a multi-value array gives #VALUE!.

#Errors

Errors propagate through calculations until IFERROR, IFNA or ISERROR handle them. Error cells get the ig-error class, sort after every value, and export as real Excel errors.

#Calculated columns

⧉{ field: 'total', type: 'currency', formula: '=[@qty]*[@price]*(1-[@discount])' }
{ field: 'share', type: 'percent', formula: '=[@total]/SUM([total])' }
{ field: 'rank',  type: 'integer', formula: '=RANK([@total],[total])' }

• Results are written into the SorlaDB documents, so calculated columns sort, filter and export like stored ones.
• Columns may depend on each other in any order; the engine sorts them by dependency. Cycles give #CIRC!.
• Recalculation is incremental: editing qty recomputes only the columns depending on qty, for that row only, unless a whole-column reference ([total]) is involved, in which case the whole column is recomputed.
• Ranges ([total]) in calculated columns cover every loaded row, not just the filtered ones (like an Excel table).
• Calculated columns are read-only unless you give them editable: true.

⧉grid.setColumnFormula('total', '=[@qty]*[@price]*1.1');   // change at runtime

#Cell formulas

When cellFormulas is on (the default) and a column is editable, typing =... stores a formula for that one cell. Its value is recalculated whenever what it references changes. Typing a plain value replaces the formula again.

⧉grid.setCellFormula(id, 'price', '=[@cost]*1.3');
grid.getCellFormula(id, 'price');    // '=[@cost]*1.3'
grid.setCellFormula(id, 'price', null);

Typing a value (or a formula) into an editable calculated column overrides that cell only, the equivalent of Excel's "calculated column exception". The rest of the column keeps the column formula.

Cells with a formula get the ig-formula class and show the formula as tooltip. When editing, the editor shows the formula text.

#Footers

⧉{ field: 'qty', footer: 'sum' }                      // sum | avg | average | count | counta | min | max | median
{ field: 'due', footer: '=TEXT(MIN([due]),"dd/mm/yyyy")' }
{ field: 'paid', footer: '=COUNTIF([paid],TRUE)' }
{ field: 'total', footer: (rows, column, grid) => rows.reduce((s, r) => s + (r.total ?? 0), 0) }

Footers aggregate the filtered rows, like SUBTOTAL in Excel, and update on every change. Shortcut and formula footers are exported to Excel as formulas.

#Conditional formats

⧉{
    field: 'total',
    conditionalFormats: [
        { formula: '=[@total] >= PERCENTILE([total],0.9)', className: 'top', style: { fontWeight: '600' } },
        { when: (value, row) => value < 0, className: 'loss' }
    ]
}
// whole rows
rowFormats: [{ formula: '=AND(NOT([@paid]),[@due]<TODAY())', className: 'late' }]

Every format whose formula returns TRUE (or whose when returns true) applies its className and/or inline style. Formats are evaluated each time the cell renders.

#Data bars, color scales and icon sets

Excel's ready-made formats place each number between the column minimum and maximum over the filtered rows (or the min / max you give), and redraw when an edit moves them:

⧉{ field: 'sales', conditionalFormats: [{ type: 'dataBar' }] }                               // bar from 0 to the maximum
{ field: 'sales', conditionalFormats: [{ type: 'dataBar', color: '#63be7b', showValue: false }] }
{ field: 'margin', conditionalFormats: [{ type: 'colorScale' }] }                           // red, yellow, green
{ field: 'margin', conditionalFormats: [{ type: 'colorScale', colors: ['#ffffff', '#1a73e8'], min: 0, max: 1 }] }
{ field: 'trend', conditionalFormats: [{ type: 'iconSet', icons: 'arrows' }] }              // ↓ → ↑ by thirds
{ field: 'score', conditionalFormats: [{ type: 'iconSet', icons: ['😞', '😐', '🙂'], thresholds: [50, 80], thresholdType: 'number' }] }

Blank and non-numeric cells get no format. Presets and rules can be mixed in the same list. The colors of bars and icons come from the --ig-databar, --ig-cf-good, --ig-cf-mid and --ig-cf-bad theme variables.

#Sparklines

⧉{ field: 'last12', sparkline: true }                                   // line
{ field: 'last12', sparkline: { type: 'bar', negativeColor: '#d93025' } }
{ field: 'results', sparkline: { type: 'winloss' } }                   // up for positive, down for negative

The value is a list of numbers ([3, 5, 2]) or text such as "3,5,2". Each row is scaled to its own range unless you set min and max. Sparklines are inline SVG; export writes the numbers as text.

#Formula filters & ad-hoc evaluation

⧉grid.setFilter('total', { formula: '=[@total] > AVERAGE([total])' });
grid.evaluate('=SUMIFS([total],[city],"Recife")');   // over the filtered rows
grid.evaluate('=[@qty]*2', id);                      // in the context of one row

#Custom functions

⧉import { IntelligentGrid, FormulaError, ERRORS } from '@andmarruda/intelligent-grid';

// every grid
IntelligentGrid.defineFunction('VAT', ([value, rate]) => {
    if (typeof value !== 'number') throw new FormulaError(ERRORS.VALUE);
    return value * (typeof rate === 'number' ? rate : 0.2);
});

// one grid
grid.registerFunction('DISCOUNTED', ([price]) => (price as number) * 0.9);
new IntelligentGrid('#grid', { functions: { DOUBLE: ([n]) => (n as number) * 2 }, ... });

• Arguments arrive evaluated: numbers, strings, booleans, null (blank), FormulaError, or arrays for ranges.
• By default an error argument short-circuits: your function is not called and the error is returned. Pass { errors: true } to receive errors.
• Throw new FormulaError(code) to return an Excel error.
• { lazy: true } receives the unevaluated AST nodes plus evaluate(node), to evaluate only some arguments (like IF).
• Function names are case-insensitive. Grid functions take precedence over global ones.

⧉IntelligentGrid.defineFunction('FIRSTOK', (args, ctx, evaluate) => {
    for (const arg of args) {
        const value = evaluate(arg);
        if (!(value instanceof FormulaError)) return value;
    }
    return new FormulaError(ERRORS.NA);
}, { lazy: true });

#Using the engine without a grid

⧉import { compile, evaluate, parse, toExcelFormula } from '@andmarruda/intelligent-grid';

const { ast } = compile('=[@qty]*[@price]');
evaluate(ast, { rowValue: f => ({ qty: 2, price: 5 })[f] ?? null, columnValues: () => [] });   // 10
toExcelFormula(parse('=[@qty]*[@price]'), (field) => ({ qty: 'B2', price: 'C2' })[field]);      // 'B2*C2'

Edit this page on GitHub