Sheets
Formulas and functions
Start a cell with =, refer to cells and ranges, and the full list of functions Nemi Sheets understands.
On this page
A formula is a cell that starts with =. Type it into the cell or into the formula bar at the top of the grid, which shows Enter a value or =SUM(A1:A5) while it is empty. As you type, a list of matching functions and tab names appears, and the cells and ranges your formula mentions light up in the grid so you can see what you are pointing at. What you type in the formula bar is written to the cell when you press Enter or Tab, or click somewhere else; Esc leaves the cell as it was.
| You can write | Example |
|---|---|
| A cell reference | =A1 |
| A range | =SUM(A1:B20) |
| A whole column | =SUM(B:B), or =SUM(B2:B) to start below a heading |
| A reference that does not move when copied | =$B$2, or =B$2 to pin only the row |
| Arithmetic | =(A1+A2)*0.21 |
| Joined text | =A1&" "&B1 |
| A comparison | =A1>=100 |
| A condition | =IF(A1>100,"over","under") |
Reference another tab#
A formula can point at cells on another tab of the same spreadsheet. Write the tab name, an exclamation mark, then the cell or the range: =Sheet2!A1 reads one cell, and =SUM(Sheet2!B:B) adds up a column that lives elsewhere. A tab name with a space in it goes in single quotes, as in ='Monthly totals'!A1:B5.
- 1
Start the formula
Type
=and whatever comes first, for example=SUM(, in a cell or in the formula bar. - 2
Click the other tab
The formula stays open in the formula bar, and a strip above the grid says which cell it is for.
- 3
Click a cell, or drag over a range
The reference is added with the tab name in front, such as
'Form responses'!B2:B40. Click again to change it. - 4
Press Enter
The formula is written to its own cell and you are back on its tab. Esc cancels the whole edit instead.
A click adds a reference only right after =, a bracket, a comma or an operator such as +. Anywhere else a click finishes the formula, the same as in the cell. Pointing works on your own tab too: while you type a formula in a cell, click another cell to add it.
- Capitalisation does not matter.
sheet2!a1finds the same tab asSheet2!A1. - Start typing a tab name and it is offered in the suggestions, already quoted when it needs to be. Right after
Orders!the suggestions list that tab's columns by the heading in their first row, and picking one writes the whole column, such asOrders!E:E. - A whole column,
Orders!E:E, or a column from a row down,Orders!E2:E, reads as far down as that tab has data, up to 10,000 rows. That is what to use on a tab whose length changes, such as a connected tab:=SUM(Orders!E:E)keeps adding up every row after a refresh brings more. - One range cannot span two tabs. Write a reference per tab and add them together instead.
- Moving or copying a formula keeps it pointing at the tab it named, so a total you fill down a column goes on reading the tab it was written against.
- Renaming a tab updates every formula that names it, so a reference never breaks because a tab got a better name. Tab names are unique, ignoring capitals, so a reference always has exactly one tab to point at.
- A tab connected to app data is read only, and this is how you use it: keep your totals and lookups on another tab and point them at it. They recalculate every time the connected tab refreshes. See Connecting a tab to app data.
The functions that exist#
This is the whole list. Anything else returns #NAME?, which is Nemi telling you it does not know that function rather than that your formula is malformed.
| Kind | Functions |
|---|---|
| Maths and statistics | SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, COUNTBLANK, PRODUCT, MEDIAN, STDEV |
| Numbers | ROUND, ROUNDUP, ROUNDDOWN, ABS, SQRT, POWER, MOD, INT, CEILING, FLOOR, EXP, LN, LOG, SIGN |
| Logic | IF, IFS, IFERROR, AND, OR, NOT, TRUE, FALSE, ISBLANK, ISNUMBER, ISTEXT |
| Text | CONCAT, CONCATENATE, TEXTJOIN, LEN, UPPER, LOWER, TRIM, LEFT, RIGHT, MID, REPT, SUBSTITUTE, TEXT |
| Conditional totals | COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS |
| Dates and times | TODAY, NOW, DATE, YEAR, MONTH, DAY, HOUR, MINUTE, EOMONTH, WEEKDAY, WEEKNUM, DATEDIF |
| Lookups | VLOOKUP, HLOOKUP, XLOOKUP, INDEX, MATCH |
AVG is accepted as another name for AVERAGE.
IF and IFERROR only work out the branch they actually need, so wrapping a lookup that might fail in =IFERROR(VLOOKUP(...),"not found") is safe and cheap. XLOOKUP takes the value to find, the column to search, the column to return, and optionally what to show when there is no match.
What the errors mean#
| Error | What went wrong |
|---|---|
#NAME? | A function name Nemi does not have. Check it against the list above, including the spelling. |
#VALUE! | The wrong kind of value went in, usually text where a number was expected, or an unreadable date. |
#DIV/0! | A division by zero, or by an empty cell. |
#REF! | A reference that does not point anywhere, often after a row or column was deleted, or a range used where a single cell belongs, such as =B:B on its own instead of inside SUM(B:B). |
#N/A | A lookup found nothing. Wrap it in IFERROR if that is a normal outcome for your sheet. |
#NUM! | A number that cannot work, such as a date range that ends before it starts. |
#CYCLE | The formula depends on itself, directly or through a chain of other cells. Nemi stops rather than looping. |
Copying formulas#
Drag the fill handle at the corner of a selection, or copy and paste a formula elsewhere, and the references shift with it: a formula in C2 reading =A2+B2 becomes =A3+B3 in C3. Put a dollar sign in front of the part you want to stay still. $A2 keeps the column, A$2 keeps the row, $A$2 keeps both.
Type =
The function list appears as soon as you start a name.
Point at cells
Click a cell, or another tab and then a cell, to add it. The ranges you mention are outlined in the grid.
Press Enter
The result lands in the cell, the formula stays in the bar.
Fill it down
Drag the corner handle. References follow, unless you pinned them.
One thing to know before you export#
Every export except .nemi writes the values your formulas produced, not the formulas themselves. A CSV, a PDF or a Markdown table of a sheet is a picture of the answers. Excel and OpenDocument exports carry every tab, also as values. If you want the formulas to survive a round trip, keep the file as .nemi. This is covered in full in Importing and exporting.
Related
Still stuck? Email support@nemilab.com and tell us what you were trying to do.