Calculate values using data in table cells

1. Double-tap the cell where you want the result to appear, then tap to the left of the input bar to open the keyboard for formulas.

   The formula editor and keyboard appear.

   To quickly add the values in a column, use the first empty cell at the bottom of the column as the results cell.

2. Tap SUM in the keyboard.

3. To adjust the range of cells included in the sum, drag the cell selection handles.

4. To add values in nonadjacent cells or cell ranges, select one cell or a range of cells, then tap a nonadjacent cell and drag the selection handles to select another range of cells.

5. Tap in the input bar if you’re done, or tap or in the keyboard to continue entering data in another cell.

   If you tap in the input bar, you exit the formula editor without saving your changes.

If there’s an error in your formula, appears in the result cell. Double-tap it to see the error message above the input bar.

You can create simple or complex arithmetic formulas to perform calculations on the values in your tables.

1. Double-tap the cell where you want the result to appear, then tap to the left of the input bar to open the keyboard for functions.

   The formula editor and keyboard appear.

2. Select a cell to use as the first argument in your formula, or type a value.

3. Tap an arithmetic operator, then select a cell to use as the next argument in your formula, or type a value.

   By default, Numbers inserts a + between cell references.

4. Repeat steps 2 through 3 until you complete your formula.

5. Tap in the input bar if you’re done, or tap or in the keyboard to continue entering data in another cell.

   If you tap in the input bar, you exit the formula editor without saving your changes.

If there’s an error in your formula, appears in the result cell. Double-tap it to see the error message above the input bar.

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.”

1. Double-tap the cell where you want the result to appear, then tap to the left of the input bar to open the keyboard for functions.

   The formula editor and keyboard appear.

2. Select a cell to use as the first argument in your formula, or type a value.

3. Tap the button below the arithmetic operators to open the keyboard displaying comparison operators.

4. Tap a comparison operator, then select a cell to use as the next argument in your formula, or type a value.

5. Tap in the input bar if you’re done, or tap or in the keyboard to continue entering data in another cell.

   If you tap in the input bar, you exit the formula editor without saving your changes.

If there’s an error in your formula, appears in the result cell. Double-tap it to see the error message above the input bar.

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 value(s) 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).

The examples below show the use of cell references in formulas.

• If the referenced range is more than one cell, the starting and ending 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 multiplies the sum of the column named “Number of Guests” by the value in cell B2 in a table on another sheet.

You can use any of the more than 250 predefined mathematical functions included with Numbers in your spreadsheets. The Functions Browser includes examples showing how the functions work to help you choose one that suits your needs.

1. Double-tap the cell where you want the result to appear, then tap to the left of the input bar to open the keyboard for functions.

   The formula editor and keyboard appear.

2. Tap functions in the keyboard, then tap Categories in the list of functions that appears.

3. Tap a category, then tap a function to insert it in the input bar. Scroll to see the entire list within a category.

   If you’re not sure which function to use, tap next to any function to read more details about it.

   For quick access to functions you recently used, tap the Back button to return to the top level, then tap Recent.

4. After you select a function, tap each token in the input bar, then select a cell or cell range to replace the argument name with a cell address or other data, as needed.

   

5. Tap in the input bar if you’re done, or tap or in the keyboard to continue entering data in another cell.

   If you tap in the input bar, you exit the formula editor without saving your changes.

If there’s an error in your formula, appears in the result cell. Double-tap it to see the error message above the input bar.

You can edit an existing formula so that it refers to different cells.

1. Double-tap the result cell with the formula you want to edit.

   The formula editor and keyboard appear.

2. Do any of the following:

   • Add more cell references: Place the insertion point within the argument area for the formula, then select the new cells you want to add.

   • Remove cell references: In the formula editor, select the unwanted cell references, then tap .

   • Change the cell references: Select the existing cell addresses you want to change, then tap the new cells.

3. Tap in the input bar if you’re done, or tap or in the keyboard to enter data in another cell.

If there’s an error in your formula, appears in the result cell. Double-tap it to see the error message above the input bar.

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, then when 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.

1. Double-tap the cell with the formula whose cell addresses you want to maintain.

   The formula editor and keyboard appear.

2. Tap the triangle on the token representing the cell range you want to preserve.

   

3. Turn on Preserve Row or Preserve Column for the beginning or ending addresses of the selected range.

   

   If you change the number of rows or columns in the table, or if you move the formula to a different cell, the preserved row or column references are adjusted, but they maintain their absolute original cell references.