Writing formulas

A formula calculates a value from other values. Nixt Sheets evaluates formulas the way Excel does, including Excel’s own quirks, so a workbook gives the same answers here as in the file it came from. For the list of functions, see the Function reference.

Enter a formula

1. Click the cell.
2. Type = followed by the formula, for example =B2*C2.
3. Press Enter.

The cell shows the result; the formula bar shows the formula as you typed it. Function names, cell references and defined names can be typed in any capitals: =sum(a1:a3) works the same as =SUM(A1:A3).

Values in formulas

Arguments are separated by commas. You can leave an argument out and keep its comma, as in =OFFSET(A1,,2); it is treated as blank.

Operators

From the first applied to the last:

Use brackets to change the order: =(A1+B1)*C1.

As in Excel, negation is applied before powers, so =-2^2 is 4, not −4. Write =-(2^2) for −4. Operators of the same level work left to right, including ^: =2^3^2 is 64.

A comparison between text and text ignores capitals. An empty cell counts as 0 and as empty text, so =A1=0 is TRUE when A1 is empty.

References

Cells and ranges

Relative and absolute references

A $ fixes the column or row when a formula is copied or filled:

References are adjusted when you copy and paste or fill a formula, and when you insert or delete rows and columns. A cut-and-paste formula keeps pointing at the same cells.

Other sheets

Put the sheet’s name and an exclamation mark before the reference. Quote the name in single quotes if it contains spaces or punctuation, and double any apostrophe inside it.

=Summary!B4
='Q1 figures'!B4
='Alex''s sheet'!A1:A10

Several sheets at once

A 3-D reference covers the same cells on a run of sheets, from the first sheet named to the last in tab order:

=SUM(Jan:Mar!B4)

Other workbooks

A workbook made in another app can contain references to another workbook, such as =[1]Budget!B4. Nixt Sheets shows the value the workbook remembers for that cell. See Links to other workbooks.

Insert a function

The function browser

1. Click the cell.
2. Open the Functions panel: click Insert a function in the formula bar, the browse button in Formulas › Function library, or Function browser in the More menu.
3. Type part of a function’s name in the search box (Search … functions). With the box empty, the panel lists frequently used functions such as SUM, AVERAGE, VLOOKUP and XLOOKUP.
4. Click a function.

Each entry shows the function’s syntax and what it is for. Clicking one starts editing the cell with = and the function’s name and opening bracket, ready for its arguments. Names that start with what you typed are listed first, then names that contain it, up to 40 results. The search lists current function names; older names such as NORMDIST work when typed.

By category

1. Click the cell.
2. On the Formulas tab, in Function library, click Financial, Logical, Text, Date, Lookup, Maths or Stats. Hover over a button to see how many functions it holds.
3. In the dialog, click a function. Click Cancel to close without choosing.

The function is inserted the same way. Functions you choose this way are added to the Recent menu, which appears in Function library once it has something in it and lasts until you close the workbook.

Engineering, information and database functions have no category button; find them with the function browser.

AutoSum

Formulas › AutoSum writes Sum, Average, Count, Max or Min of the numbers directly above the current cell. See Entering and editing data.

Dynamic arrays and spilling

A formula whose result is a block of values — =SORT(A2:A20), =UNIQUE(B2:B50), =SEQUENCE(5), or =A2:A10*2 — spills: the results fill the cells below and to the right of the formula.

• Only the top-left cell holds the formula. The other cells show values but can’t be typed into; trying says That value spills from cell**. Edit the formula there, or clear it first.**
• If something is in the way, the formula shows #SPILL!. Clear the cells that block it and the results appear.
• When the result gets smaller, the cells it no longer needs are emptied.
• Spilled cells take the formatting of the formula’s cell.
• Saved to .xlsx, a spilling formula is stored as one formula, as Excel stores it.

Structured references

Formulas can refer to a table’s columns by name. See Tables, slicers and timelines.

A structured reference follows the table: inserting a column to the left of Amount doesn’t change what Sales[Amount] means, and a table that grows is covered automatically. Renaming the table rewrites the formulas that name it.

Defined names

A defined name stands for a cell, a range or a formula, so =SUM(Revenue) can replace =SUM(Summary!$B$2:$B$13).

Create and delete names

1. Select the cells the name should refer to.
2. Click Names in Formulas › Defined names. Once the workbook has names, the button shows how many, such as 4 names.
3. In the Names dialog, type the name in Name, for example Revenue.
4. Check Refers to. It starts as the selection’s absolute address, such as Summary!$B$2:$B$13, and you can type any reference or formula.
5. Click Add.
6. Click Done.

The dialog lists every name with what it refers to. Click the bin beside a name (Delete followed by the name) to delete it. Adding a name that already exists, in any capitals, replaces it. The status bar says, for example, Revenue refers to Summary!$B$2:$B$13.

A name that looks like a cell reference, such as Q1 or AB12, is refused: ”…” looks like a cell reference, so it cannot be a name.

Create names from labels

If a block already has labels in its top row or left column, you can make names from them all at once.

1. Select the block, including the labels.
2. Open Formulas › Defined names › From selection and choose Top row, Left column or Both.

Each label becomes the name of the cells below it (for Top row) or to its right (for Left column). Spaces in a label become underscores, so Cost of sales becomes Cost_of_sales. Labels that can’t be names — empty, starting with a digit, containing punctuation, or looking like a cell reference — are skipped. The status bar says how many names were made, or None of those labels can be a name — they must start with a letter and hold no spaces.

Use a name

• Type it in a formula: =SUM(Revenue).
• Or, while typing a formula, open Formulas › Defined names › Use in formula and choose the name. It is added to the end of what you are typing. If you aren’t editing a cell, it starts a formula in the current cell.
• Jump to a name’s cells with Home › Editing › Find › Go to….

LET and LAMBDA

LET names parts of a calculation inside one formula, so a long sub-expression is written once:

=LET(total, SUM(B2:B13), tax, total*0.2, total+tax)

LAMBDA makes a function of your own. Give it a defined name and call it like a built-in function:

1. Open Formulas › Defined names › Names.
2. In Name, type DOUBLE.
3. In Refers to, type =LAMBDA(x, x*2).
4. Click Add, then Done.
5. In a cell, type =DOUBLE(21).

MAP, REDUCE, SCAN, BYROW, BYCOL and MAKEARRAY take a LAMBDA and apply it across an array:

=MAP(A2:A10, LAMBDA(v, v*1.2))
=REDUCE(0, B2:B13, LAMBDA(acc, v, acc+v))
=BYROW(B2:E10, LAMBDA(row, MAX(row)))

Error values

When a formula can’t produce a result, the cell shows an error value. Errors pass through the formulas that use them, so one error can show up in many cells; trace it back with Auditing formulas.

Recalculation

In Automatic calculation, the default, changing a cell recalculates the formulas that depend on it straight away.

For a large model where each change causes a pause, switch to manual calculation:

1. Open the calculation mode menu in Formulas › Calculation (it shows Automatic).
2. Choose Manual.

In manual calculation, formulas keep their old results until you ask. When results are out of date, Calculate appears in the status bar. To recalculate:

Switching back to Automatic recalculates everything straight away. Saving recalculates first in either mode, so a saved file never holds out-of-date results.

Use Calculate now also to refresh functions such as NOW, TODAY and RAND.

Compatibility with Excel

Formulas follow Excel’s rules where they differ from ordinary arithmetic, so results match the workbook’s origin:

• =-2^2 is 4.
• MOD takes the sign of the divisor: =MOD(-3,2) is 1.
• VLOOKUP and HLOOKUP look for an approximate match unless the last argument is FALSE.
• MAX and MIN over cells that hold no numbers give 0.
• Dates are numbers counted from 1 January 1900, and 1900 is treated as a leap year, as Excel treats it. Workbooks that use the 1904 date system keep it.
• IF, IFS, IFERROR, IFNA, CHOOSE and SWITCH calculate only the branch they use, so =IF(A1=0,"",1/A1) never shows #DIV/0!.
• Older statistical function names, such as NORMDIST and TINV, work in existing workbooks.