Entering Formulas in Worksheet Cells
Use formulas 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.
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 no argument is present. Formula syntax is described in the following table.
|
Symbol
|
Explanation
|
Example
|
|
General Syntax
|
Equal sign =
|
Begins each formula; also indicates a value for a variable
|
=CriteriaVolume(t=wall)
|
|
Parentheses ( )
|
Encloses a function argument; also used in arithmetic equations
|
=acos(0.6)
=A6+(A6*.07)
|
|
Square brackets [ ]
|
Encloses a record destination
|
=R IN ['myformat']
|
|
Period .
|
Separates a record identifier and a field identifier
|
=Furniture.Type
|
|
Colon :
|
Separates path name levels in cell references
|
=MyWorksheet:A1
|
|
Comma or semicolon
, or ;
|
Separates multiple values in a function argument; use a semicolon when commas are used as decimal separators by the operating system
|
=sum(A2,E3)
=sum(A2;E3)
|
|
Single quote '
|
Encloses a string constant
|
=Appliances.'Model #'
|
|
Dollar sign $
|
Designates an absolute reference
|
=A4*$B$1
|
|
Double period ..
|
Designates a range of cells
|
=sum(A10..A12)
|
|
Arithmetic Operators
|
Plus sign +
|
Addition
|
=A6+A8
|
|
Hyphen -
|
Subtraction
|
=A6-A8
|
|
Asterisk *
|
Multiplication
|
=A6*.06
|
|
Forward slash /
|
Division
|
=B3/12
|
|
Caret ^
|
Exponentiation
|
=13^2
|
|
DIV
|
Integer division (returns the integer quotient of the division operation)
|
j:= 36 DIV 5;
|
|
MOD
|
Remainder division (returns the remainder of the division operation as an integer)
|
k:= 36 MOD 5;
|
|
Comparison Operators
(used with IF function)
|
Equal sign =
|
Equal
|
=if((L='L2'),Area,0)
|
|
Less than and greater than signs (or Option+ = on Mac)
<> or 
|
Not equal
|
=if((S<>'Dryer'),B9,0)
|
|
Less than sign <
|
Less than
|
=if((C7<100),100,C7)
|
|
Less than and equal signs (or Option+ < on Mac)
<= or 
|
Less than or equal to
|
=if((E2<=G2),0.05,G2)
|
|
Greater than sign >
|
Greater than
|
=if((C7>100),100,C7)
|
|
Greater than and equal signs (or Option+ > on Mac)
>= or 
|
Greater than or equal to
|
=if((E2>=G2),0.05,G2)
|
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.
Error Code
|
Explanation
|
|
#NAME?
|
The field name in a database header row definition does not exist in the file; see Retrieving Record Information in a Worksheet
|
|
#VALUE!
|
The argument is the wrong type of data for the formula; for example, a cell referenced in a mathematical formula contains text
|
|
#CVAL?
|
A cyclical reference cannot be resolved
|
|
?Result?
|
The result value type is unrecognized
|
|
#OPCODE?
|
Internal error
|
|
#DIV 0!
|
Division by zero error encountered
|
|
#FAC?
|
Unrecognized entry
|
|
#OBJ!
|
The worksheet name in an external reference does not exist, or the record name does not exist
|
|
#CSTATUS?
|
Functions are nested too deeply (more than ten levels deep)
|
To manually enter a formula:
1. Select the cell.
2. Enter an equal sign (=), and then enter the formula. The entries automatically display in the worksheet Formula bar. A formula can consist of functions, operators, cell references, and constant values.
3. 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.
4. The formula executes as soon as the cell entry has been validated (Auto-recalc must be selected in the worksheet preferences; see Preferences).
To enter a formula with the Function and Criteria commands:
1. Select the cell.
2. Enter an equal sign (=). The entry automatically displays in the worksheet Formula bar.
3. Select Insert > Function from the Worksheet menu.
The Select Function dialog box opens.
4. Select a function from the list and click OK.
The formula is placed in the worksheet Formula bar and the cursor is placed between the parentheses, awaiting an argument, if necessary.
5. 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.
6. 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 click OK. Proceed to step 8.
● To specify other criteria, or to use attributes of other objects in the drawing, click the Custom button.
7. The Criteria dialog box opens. Set each of the three fields to the desired selection criteria. Click More Choices to specify additional criteria. Click Fewer Choices to remove added criteria. Click OK to add the criteria to the function argument.
8. 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.
9. The formula executes as soon as the cell entry has been validated (Auto-recalc must be selected in the worksheet preferences; see Preferences).
~~~~~~~~~~~~~~~~~~~~~~~~~
Entering Data in Spreadsheet Cells
Entering Data in Database Rows
Entering Constant Values in Worksheet Cells
Referencing Other Worksheet Cells
Worksheet Functions
www.vectorworks.net