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 function name a list of matching functions appears with its signature, and the cells and ranges your formula mentions light up in the grid so you can see what you are pointing at.
| You can write | Example |
|---|---|
| A cell reference | =A1 |
| A range | =SUM(A1:B20) |
| 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") |
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. |
#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
The ranges you mention are outlined in the grid while you type.
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.