Calculate values using data in table cells in Numbers on Mac
You can create formula or function cells that automatically perform calculations using the data in any cells you select. For example, you can compare values in two cells, calculate the sum or product of cells and so on. The result of a formula or function appears in the cell where you entered it.
You can also use any of the predefined mathematical functions included with Numbers to create formulas. There are more than 250 functions for applications including statistics, engineering and finance, some of which retrieve information remotely via the internet. Detailed information about each function appears in Formulas and Functions Help online and in the Functions Browser, which appears when you type an equal sign (=) in a cell.
Insert a formula
You can create simple or complex arithmetic formulas to perform calculations on the values in your tables.
Go to the Numbers app
on your Mac.In a spreadsheet, click the cell where you want the result to appear, then type the equal sign (=).
The Formula Editor opens. Drag the left side of the Formula Editor to move it. You can resize it by dragging from any of its outer edges.
Click a cell to use in your formula or type a value (for example, a number such as 0 or 5.20).
Type an arithmetic operator (for example, +, -, *, or /), then select another cell to use in your formula or type a value.
By default, Numbers inserts a + between cell references.
Continue adding operators and cell references until your formula is complete, then press Return or click
in the Formula Editor when you’ve finished.If you click
, you exit the Formula Editor without saving your changes.
If there’s an error in your formula, 
appears in the result cell. Click it to see the error message. If the message indicates another cell is causing the error, you can click the cell reference to select the cell with the error.
Insert a function
Go to the Numbers app
on your Mac.In a spreadsheet, click the cell where you want the result of the function to appear, then type the equal sign (=).
You can also click
in the toolbar, then choose New Formula.The Formula Editor opens, and the Functions Browser appears on the right side of the window, displaying a list of all the functions. For help with a function, click it.
Drag the left side of the Formula Editor to move it. You can resize it by dragging from any of its outer edges.
Type a function name (or terms associated with the function such as “address”) in the search field at the top of the Functions Browser or browse the available functions, then double click the name of the function you want.
The function appears in the Formula Editor with all the required and optional arguments for the function.
You can get help for the function in the Functions Browser, choose values for the arguments, show the formula as text temporarily or convert it to text permanently by clicking arrows in the Formula Editor (as shown below).
Converting the formula to text closes the Formula Editor and leaves the text version of the formula in the cell.
When you edit a cell that contains a formula, the Smart Cell View at the bottom of the window shows you the formula result, cell reference values, errors and warnings.
Select an argument in the function, then enter a value or select cells to include in the calculation by doing one of the following:
Select a cell: Click the cell.
Select a range of cells across multiple rows and columns: Drag across the range of cells you want to include.
Add the values of a single column or row: Click the bar at the top of the column or the left of the row — or select all the cells in the column or row.
Press Return or click
in the Formula Editor when you’ve finished.If you click
, you exit the Formula Editor without saving your changes.
If there’s an error in your formula, appears in the result cell. Click it to see the error message. If the message indicates another cell is causing the error, you can click the cell reference to select the cell with the error.
Compare values using a formula
You can use comparison operators to check whether the values in two cells are equal, or if one value is greater or less than the other. To do this, you must set up a statement within a cell — for example A1 > A2, meaning the value in cell A1 is greater than the value in cell A2. The result of the comparison operator is expressed as “true” or “false”.
Go to the Numbers app
on your Mac.In a spreadsheet, click the cell where you want the comparison result to appear, then type the equal sign (=).
The Formula Editor opens. Drag the left side of the Formula Editor to move it. You can resize it by dragging from any of its outer edges.
Click a cell, the value of which you want to compare, or type a value to compare.
Type a comparison operator (>, >=, =, <>, <, or <=), then select a cell, the value of which you want to compare, or type a static value to compare.
Press Return or click
in the Formula Editor when you’ve finished.If you click
, you exit the Formula Editor without saving your changes.
If there’s an error in your formula, appears in the result cell. Click it to see the error message. If the message indicates another cell is causing the error, you can click the cell reference to select the cell with the error.
Refer to cells in formulas
In your formulas, you can include references to cells, ranges of cells and whole columns or rows of data — including cells in other tables and on other sheets. Numbers uses the values in the referenced cells to calculate the result of the formula. For example, if you include “A1” in a formula, it refers to the value in cell A1 (the cell in Column A and Row 1).
Note: If your table uses categories and you add a new row within a range of reference cells, the formula results won’t include the new row unless you change the cell reference.
The examples below show the use of cell references in formulas.
If the referenced range is more than one cell, the start and end cells are separated by a single colon.
COUNT(A3:D7)
If the reference is to a cell in another table, the reference must contain the name of the table (unless the cell name is unique within all tables).
Table 2::B2
Note that the table name and cell reference are separated by a double colon (::). When you select a cell in another table for a formula, the name of the table is automatically included.
If the reference is to a cell in a table in another sheet, the sheet name must also be included (unless the cell name is unique within all the sheets).
SUM(Sheet 2::Table 1::C2:G2)
The sheet name, table name and cell reference are separated by double colons. When you click a cell in another sheet while building a formula, the name of the sheet and the name of the table are automatically included in the formula.
To refer to a column, you can use the column letter. The formula below calculates the total of the cells in the third column:
SUM(C)
To refer to a row, you can use the row number. The formula below calculates the total of the cells in the first row:
SUM(1:1)
To refer to a row or column with a header, you can use the header name. The formula below totals all the cells in the row with the header “Revenue”:
SUM(Revenue)
The formula below refers to a column by its header name, “Number of Guests”, and cell B2 in a table on another sheet.
Preserve row or column references in formulas
You can set row and column references in a formula to be absolute so that you can use the same formula elsewhere in your spreadsheet without the cell references changing, as they would otherwise.
If you don’t preserve the row or column references, if you move the formula (by cutting and pasting or by adding new rows and columns), the references are adjusted relative to the formula’s new location.
Go to the Numbers app
on your Mac.In a spreadsheet, double-click the result cell with the formula you want to edit.
The Formula Editor opens, displaying the functions. Drag the left side of the Formula Editor to move it.
Click the triangle on the token representing the cell range you want to preserve.
Select Preserve Row or Preserve Column for the beginning or ending addresses of the selected range.
Alternatively, you can select the range in the Formula Editor, then press Command-K to preserve both the row and column of the range.
Press Return or click
in the Formula Editor when you’ve finished.If you click
, you exit the Formula Editor without saving your changes.
Change an existing formula
You can edit an existing formula so that it refers to different cells.
Go to the Numbers app
on your Mac.In a spreadsheet, double-click the result cell with the formula you want to edit.
The Formula Editor opens, displaying the formula. Drag the left side of the Formula Editor to move it.
Do any of the following:
Change the cell references: Select the existing cell addresses you want to change, then select the new cells.
Remove cell references: In the Formula Editor, select the unwanted cell references, then press Delete on your keyboard.
Add more cell references: Place the insertion point within the argument area for the formula, then click the new cells you want to add.
Press Return or click
in the Formula Editor when you’ve finished.If you click
, you exit the Formula Editor without saving your changes.
If there’s an error in your formula, appears in the result cell. Click it to see the error message. If the message indicates another cell is causing the error, you can click the cell reference to select the cell with the error.