Write formulas

Write Excel-style formulas, refer to other sheets, and look up workspace records with AXLOOKUP.

Calculate values with Excel-style formulas, refer to other cells and sheets, and look up records from your workspace.

Before you start: Can edit on Sheets.

Steps

  1. Click a cell and type =. A formula always starts with =.
  2. Type a function name. A list of matching functions appears with a hint for the arguments. Press Tab or Enter to accept one. Use the arrow keys to choose.
  3. While typing, click or drag cells to insert references such as A1 or A1:B4 at the cursor.
  4. Press Enter. The result shows in the cell, and the formula stays in the formula bar.

Example: =SUM(B2:B20) or =IF(C2>1000,"Large","Small").

References

Write Meaning
A1 One cell. $A$1 locks it.
A1:B4 A range.
A:A, B:D Whole columns. They grow with the data.
Data!B2, 'My Sheet'!B2 A cell on another sheet. Quote the name if it has spaces.

Operators

+, -, *, /, ^ (power), & (join text), and comparisons =, <>, <, <=, >, >=. A leading - or + works on numbers.

Criteria (COUNTIF, SUMIF, AVERAGEIF)

Write the test as text: ">10", "<=5", "<>paid", or just "paid". Wildcards are not supported.

Look up a record: AXLOOKUP

AXLOOKUP(type, key_field, key, target_field) reads one field of one record in your workspace.

Example: =AXLOOKUP("orders","number",A2,"amount") finds the order whose number equals the value in A2 and returns its amount.

Important: AXLOOKUP runs with the permissions of the person who last recalculated the sheet. The looked-up value is saved in the cell, so anyone who can open the workbook can see it.

Errors

Errors are shown as values and can be caught with IFERROR.

Error Meaning
#DIV/0! Division by zero, or an average of nothing.
#VALUE! Wrong kind of value or wrong number of arguments.
#NAME? Unknown function.
#REF! The reference is invalid, for example its row was deleted.
#N/A A lookup found nothing.
#CYCLE! Cells depend on each other in a loop.

What happens next

  • Every edit recalculates the cells that depend on it. A change and everything it affects are saved together.
  • A formula result is stored in the cell. If it does not fit the column type (for example text in a Number column) the stored value is empty, but the formula still shows its result.
  • Inserting or deleting rows updates the references in your formulas.
  • Text that looks like a number in a Text column works as a number in formulas.

Limits

A formula can be up to 2,000 characters.

Tips

Troubleshooting

#NAME? Check the spelling of the function.

My lookup shows #N/A VLOOKUP and MATCH need an exact match. Check spaces and spelling.