Formulas

The formula engine is a spreadsheet's calculation in the page: sheets of values and formulas, kept worked out with no server. Over four hundred Excel functions — SUMIFS, XLOOKUP, IRR, LET, LAMBDA, FILTER, SORT — dynamic arrays that spill, names and tables, and a dependency graph that recalculates only the formulas a change touches.

The formula bar edits a cell as a spreadsheet does: colour for every part of a formula, each reference in its own colour, completion for functions and names, the current argument shown in the function's signature, and F4 for $ references. Workbooks open from Excel and save to it, formulas and all.

A quote that works itself out

Change a quantity, the discount band or the delivery and every figure follows. No script runs this quote: the engine holds the sheet, inputs marked data-bmx-cell edit cells, and anything else marked that way shows a cell's value in its format.

Item	Price	Quantity	Line
Standing desk
Task chair
Monitor arm
Desk lamp

Goods ( items)
Discount
Delivery
VAT at
Total

Save as Excel

Open an Excel file…

Text is read as a person means it. Type 10, 1,000 or 5% and the engine files a number. The discount band is an XLOOKUP with a match mode that finds the next band down; the sentence under the totals is TEXT and &. Save the quote and open it in Excel: the formulas are there, not just their values.

Get the code

<script src="/assets/bmx-components.min.js"></script>

<bmx-formula-engine
  id="fx-quote-engine"
  bind="#fx-quote"
  locale="en-GB"
  sheets='[{"name":"Quote","cells":{
    "A1":"Item","B1":"Price","C1":"Qty","D1":"Line",
    "A2":"Standing desk","B2":449,"C2":2,"D2":"=B2*C2",
    "A3":"Task chair","B3":289,"C3":4,"D3":"=B3*C3",
    "A4":"Monitor arm","B4":89,"C4":4,"D4":"=B4*C4",
    "A5":"Desk lamp","B5":39,"C5":6,"D5":"=B5*C5",
    "F1":"From","G1":"Discount",
    "F2":0,"G2":0,"F3":1000,"G3":0.05,"F4":2500,"G4":0.08,"F5":5000,"G5":0.12,
    "A7":"Goods","D7":"=SUM(D2:D5)",
    "A8":"Discount","C8":"=XLOOKUP(D7, F2:F5, G2:G5, 0, -1)","D8":"=-ROUND(D7*C8, 2)",
    "A9":"Delivery","B9":"Standard","D9":"=IFS(B9=\"Collect\", 0, D7+D8>=1500, 0, B9=\"Express\", 39, TRUE, 19)",
    "A10":"Net","D10":"=D7+D8+D9",
    "A11":"VAT","C11":0.2,"D11":"=ROUND(D10*C11, 2)",
    "A12":"Total","D12":"=D10+D11",
    "A13":"Items","D13":"=SUM(C2:C5)",
    "A14":"Saving","D14":"=IF(D8<0, \"You save \" & TEXT(-D8, \"£#,##0.00\") & \" (\" & TEXT(C8, \"0%\") & \")\", \"Spend \" & TEXT(XLOOKUP(D7, F2:F5, F2:F5, 0, 1)-D7, \"£#,##0\") & \" more for a discount\")"}}]'
></bmx-formula-engine>

A formula bar on any grid

Click a cell, then edit its formula. Type =SU for suggestions, a function name and ( to see its arguments, F4 on a reference to cycle its $s, and Shift+F3 for the function picker. While a formula is open, clicking a cell puts its reference in — each reference in its own colour, outlined in the table too.

The bar is a combobox to a screen reader: suggestions are announced as they change, and the function's signature with the current argument is read out with the formula. Target is a name defined on the workbook; type =Tar to see it offered with its comment.

Get the code

<script src="/assets/bmx-components.min.js"></script>

<bmx-formula-engine
  id="fx-sheet-engine"
  locale="en-GB"
  sheets='[{"name":"Sales","cells":[
    ["Region","Q1","Q2","Q3","Q4","Year","Share"],
    ["North",120,140,135,150,"=SUM(B2:E2)","=F2/$F$7"],
    ["South",95,130,160,170,"=SUM(B3:E3)","=F3/$F$7"],
    ["East",210,180,205,220,"=SUM(B4:E4)","=F4/$F$7"],
    ["West",80,95,110,130,"=SUM(B5:E5)","=F5/$F$7"],
    ["Online",60,90,140,190,"=SUM(B6:E6)","=F6/$F$7"],
    ["Total","=SUM(B2:B6)","=SUM(C2:C6)","=SUM(D2:D6)","=SUM(E2:E6)","=SUM(F2:F6)","=SUM(G2:G6)"],
    ["Target","","","","","=Target","=F7/F8"]],
    "formats":{"G2:G8":"0.0%"}}]'
  names='[{"name":"Target","formula":"2800","comment":"The year&apos;s sales target"}]'
></bmx-formula-engine>
<bmx-formula-bar id="fx-bar" engine="fx-sheet-engine" cell="Sales!F7" label="Formula for the chosen cell"></bmx-formula-bar>

Dynamic arrays, LET and LAMBDA

A formula can give back a whole table: it spills into the cells below and beside it. Press an example or type your own formula over the orders below — the result is drawn as it would spill in a sheet.

FILTER

SORTBY

UNIQUE

GROUPBY

XLOOKUP

LET and SEQUENCE

MAP and LAMBDA

TEXTSPLIT

The orders the formulas read (A1:D13)

Get the code

<script src="/assets/bmx-components.min.js"></script>

<bmx-formula-engine id="fx-arrays-engine" locale="en-GB"></bmx-formula-engine>

A hundred thousand formulas

Fifty thousand rows, each with two formulas that read a shared rate, and a total over all of them. Move the rate and see how long the engine takes to recalculate everything that depends on it — the dependency graph means nothing else is touched.

Build 100,000 formulas

Very large models can leave the page altogether: with worker, the workbook is calculated on a Web Worker and mirrored in the page, so typing never waits for a recalculation. bmx-formulas.mjs, in the Pro and Gold downloads, runs the same engine in Node.

Get the code

<script src="/assets/bmx-components.min.js"></script>

<bmx-formula-engine id="fx-large-engine" locale="en-GB"></bmx-formula-engine>
<bmx-slider id="fx-large-rate" label="Rate" min="0" max="50" step="1" value="20" show-value="always" disabled></bmx-slider>