Click
to show/hide the syntax operators.Use formulas in both spreadsheet and database cells to evaluate and perform operations on drawing data. Formulas always begin with an equal sign (=) and consist of a combination of functions, cell references, or operators that combine values to produce a new value.
There are three ways to enter a function in a cell:
● In a database row cell, click the button and select a function from the Set Field dialog box.
● Manually enter a formula in the Formula bar.
● Use the Insert > Function and Insert > Criteria commands.
To quickly enter a single-function formula for a database column (for example, Volume), click the button on the cell and select the function as described in Displaying Object Properties in a Database Column.
To manually enter a formula:
Select the cell.
Enter an equal sign (=), and then enter the formula. The entries display in the worksheet Formula bar. A formula can consist of functions, operators, cell references, and constant values.
When the formula is complete, click the green check mark or press Enter to validate the entry. To cancel an entry, click the red X or press Esc.
The formula executes as soon as the cell entry has been validated (Auto-recalc must be selected in the worksheet preferences; see Preferences).
Command |
Path |
Function |
Insert (worksheet menu) |
To enter a formula with the Function and Criteria commands:
Select the cell.
Enter an equal sign (=). The entry automatically displays in the worksheet Formula bar.
Select the command.
The Select Function dialog box opens.
Select a function from the list.
The formula is placed in the worksheet Formula bar and the cursor is placed between the parentheses, awaiting an argument, if necessary.
Do one of the following:
● If the function requires numbers or text, enter the argument between the parentheses and proceed to step 8.
● If the function requires selection criteria, select Insert > Criteria from the Worksheet menu.
If an object is selected when the Criteria command is selected, the Paste Attributes dialog box opens. Otherwise, proceed to step 7.
Do one of the following:
● To use attributes of the selected object as the only selection criteria, select the attributes and proceed to step 8.
● To specify other criteria, or to use attributes of other objects in the drawing, click the Custom button.
The Criteria Dialog Box opens. Specify the criteria for the function.
When the formula is complete, click the green check mark or press Enter to validate the entry. To cancel the entry, click the red X or press Esc.
The formula executes as soon as the cell entry has been validated (Auto-recalc must be selected in the worksheet preferences; see Preferences).
Formulas must be entered with a specific syntax. If the formula is not entered correctly, the formula entry itself displays in the cell, instead of the result of the formula. Two common mistakes in syntax include forgetting to use pairs of parentheses, and omitting required commas when arguments are present. Formula syntax is described in the following table.
Click
to show/hide the syntax operators.
To force the program to treat a number as text, enclose the number in single quotation marks, as in '40'; or format the cell as Text on the Number tab of the Format Cells dialog box.
Formulas follow standard algebraic rules of hierarchy. In the following example, the value in cell C28 is first multiplied by 12, and then 4.5 is subtracted from that value. The result is then divided by 12.
=((C28*12)-4.5)/12
There are several built-in functions that can be used in formulas, including mathematical functions and functions that pull information from objects in the drawing. To use one or more worksheet functions in a formula, either enter the function manually, or use the Insert > Function and Insert > Criteria commands to select a function and selection criteria (if required) from dialog boxes. See Worksheet Functions for more information about how to use functions.
If there is a logic problem or calculation error with a formula, an error code displays in the cell.
Click
to show/hide the error codes.
In a worksheet, a formula can reference the contents of one or more other cells. The cells can be referenced within the current worksheet (internal references), or from another worksheet (external references) within the same file.
External references must include the full path name to the other worksheet. The following table shows the syntax for entering an external reference into a formula.
Syntax |
Example |
worksheet name:cell address |
=MyWorksheet:A1 |
worksheet name:range of addresses |
=SUM(MyWorksheet:A1..A12) |
If the name of the worksheet contains spaces, the name must be enclosed with single quotes as in the following example: ='Appliance Schedule':A1
To update an external reference, select File > Recalculate from the Worksheet menu.
Cell references in a worksheet can be either relative or absolute. When the formula that contains the reference is moved, an absolute reference always refers to the original cell address, while a relative reference changes depending on the location of the cell that contains the reference.
Use the dollar sign ($) character to indicate an absolute reference. The $ character locks the part of the cell reference it precedes, as described in the following table.
Combination |
Description |
$A1 |
Locks the specified column reference but leaves the row reference relative; the same column is always referred to, but the row changes if the formula is placed in a different row |
$A$1 |
Locks both the specified column and row references; regardless of where the formula is copied, it always refers to the original cell |
A$1 |
Locks the specified row reference but leaves the column reference relative; the same row is always referred to, but the column changes if the formula is placed in a different column |
In the following example worksheet, the formula =AVERAGE(B1..B3) is in cell B4. If the formula were copied to cell E9, the formula would automatically be changed to =AVERAGE(E6..E8). Because the references are relative, both the column and row would change relative to the cell where the formula is placed—always indicating the three cells directly above the formula.
~~~~~~~~~~~~~~~~~~~~~~~~~
