Grids and formulas

A Grid is a spreadsheet that lives on a Noot. Add the Grids tab from the + at the end of the project's tab strip. A Grid made inside a project's channel is listed on that project's Grids tab too - one document, not a copy. Grids are on the Business plan and above.

Formulas are written the way Excel writes them: type = in a cell or in the formula bar. Grids knows 488 functions - every function in most of Microsoft's categories, plus one that Excel does not have, ANDIF, described below. A name Grids does not know gives #NAME?, as it does in Excel.

What is covered

Counted against Microsoft's own list of Excel functions, category by category:

Help while you type

• Start a function name after = and a list opens beside what you are typing. Each row shows the function's signature - its name and what goes in the brackets - and one line saying what it does.
• The list narrows with every letter. Names that start with what you typed come first, then names that contain it.
• Up and Down move through the list. Enter or Tab puts the highlighted name in with its opening bracket. Esc closes the list and keeps what you typed.
• On a phone the list sits above the editor; tap a row to take it.

Formulas that fill several cells

A formula that returns more than one value spills: its results fill the cells below it and to its right, as they do in current Excel. =SORT(UNIQUE(A2:A50)), =FILTER(A2:C50, C2:C50>100), =SEQUENCE(12) and =A2:A10*1.2 all spill. Only the first cell holds the formula - select any other cell of the result and the formula bar shows it greyed.

• If a cell the result needs is not empty - even a formula that returns empty text - the formula shows #SPILL! instead. Clear the cells in the way and it fills again. A result that would run past the edge of the worksheet is #SPILL! too.
• D2# means everything D2 spilled, however many cells that is right now: =SUM(D2#), =ROWS(D2#).
• Arithmetic on ranges works inside a function without any special key: =SUM(A2:A4*B2:B4) multiplies the pairs and adds them up.

LET and LAMBDA

LET gives a value a name inside one formula, so it is worked out once and then used by name: =LET(total, SUM(A2:A20), total*1.2). LAMBDA writes a small function of your own. Call it straight away - =LAMBDA(x, x*2)(5) is 10 - give it a name inside a LET, or hand it to MAP, REDUCE, SCAN, BYROW, BYCOL or MAKEARRAY.

ANDIF, which Excel does not have

ANDIF is like AND, except that it stops at the first test that is FALSE, so the tests after it are never worked out. =AND(B2<>0, A2/B2>1.5) is #DIV/0! when B2 is 0, because AND works out every test. =ANDIF(B2<>0, A2/B2>1.5) is FALSE.

Where Grids differs from Excel

Functions that are not available

These Excel functions give #NAME? in a Grid. Some cannot work here at all; some are not built yet.