Using Formulas in Table Cells

In tables, you can use formulas in cells that are calculated based on the values from other cells. The cell that contains a formula displays the calculated value.

If the formula returns an error, the cell displays "####".

A formula is an expression that calculates a value based on the values from the cells specified in the expression.

The following functions are available and can be used in formulas:

• Sum. Calculates the sum of the values from the defined cells.
• Average. Calculates the average of the values from the defined cells.
• Count. Counts cells with numbers.

Additionally, you can insert expressions to calculate results based on the values from the specified cells and range of cells.

A formula must start always with the equal sign (=) and can contain the following signs:

Specifying Cells Within Formulas

You can specify cells and ranges of cells within a formula by selecting them or by referring to them using the column and row labels.

• To refer to a cell in a formula, use its column letter and row number.

  Example: A1, D2, C3

• To specify a range of cells in a formula, use the first and the last cells of the range separated with a colon “:”.

  Example: A2:B4

• To apply a formula to multiple ranges, use the function followed by one or more ranges within the table in parenthesis separated by commas.

  Example: Sum (A2:B2, A3:B3)

Changing the Cell Data Type

Every cell has an assigned data type.

You can change the data type using the editing tools from the Edit Table toolbar.

The default data type is General, but you can change it in the Properties palette.

The default data type is General, but you can change it in the Properties palette or using the editing tools from the Edit Table contextual toolbar.

The default data type is General, but you can change it in the Properties palette or using the editing tools from the Edit Table contextual toolbar.

Note: You cannot change the data type of a cell that contains a formula.

The following data types are available:

• Angle
• Currency
• Date
• Decimal number
• General
• Note
• Percentage
• Point
• Whole number

Note: If a formula cannot identify the data type, it returns an error and the cell displays "####".

To change the cell data type:

1. In the graphics area, click inside a table cell to select it.
2. On the Edit Table toolbar, select the data type from the Data Type flyout.

Inserting Formulas

You can insert a formula into a cell with several methods:

• Manually
• Using formula tools

Note: You cannot insert formulas in locked cells.

Inserting Formulas Manually

To insert formulas manually:

Note: The formula must start with the equal sign (=).

1. Select a table cell to edit it in place:
   • Click inside a cell to select it and type = (equal sign).

     - or -

   • Double-click a cell.

   The cell switches to editing mode and the editing tools appear.

2. Type the formula.

   You can use functions as in the following examples:

   • =Sum(C3:D8). Adds the values from the cells in rows 3 through 8 in columns C and D.
   • =Average(C3:D8). Calculates the average of the values from the cells in rows 3 through 8 in columns C and D.
   • =Count(C3:D8). Counts the number of cells in rows 3 through 8 in columns C and D.
   • =(C3+D3)/2. Adds the values of cell C3 and D3 and divides the result by 2.
3. Press Enter to calculate the result.

Inserting Formulas Using Formula Tools

Formulas are available for a selected cell in the context menu and on a contextual toolbar or ribbon, according to the selected user interface.

Formulas are available for a selected cell on a contextual toolbar.

Formulas are available for a selected cell on a contextual toolbar.

You can also use these flyouts on the Edit Table contextual toolbar to insert data types and formulas.

You can also use these flyouts on the Edit Table contextual toolbar to insert data types and formulas.

• Formula flyout . Lists the available functions.

To insert formulas using formula tools:

1. Click inside a table cell to select it.
2. Do one of the following:
   • On the Edit Table contextual toolbar, click the Formula flyout and select the formula.
   • Right-click and click Sum, Average, Count, or Cell.
3. Specify the cell or the range of cells corresponding to the selected formula.
4. Press Enter to calculate the result.

Example:

The following table shows how to calculate the sum for each column.