Extensions & Quantity Worksheet Formula Syntax
The Item Properties Window allows formulas to be used to define the "quantity" for an item. The syntax for these formulas is similar to the syntax used in Excel or other spreadsheet applications. The major difference is that instead of using cell numbers as operands, Dimension uses Quantity Worksheet operands. Quantity worksheet operands are made up of the following components:
| Operand | Description |
|---|---|
| Area | The total of the areas of all measurements assigned to the item. |
| Count | The total of the point counts of all measurements assigned to the item. |
| Length | The total of the lengths of all measurements assigned to the item. |
| Perim or Perimeter | The total of the perimeters of all measurements assigned to the item. |
| GroupCount | The total of the number of groups of disconnected points in all measurements assigned to the item. If points 1 to 7 in a measurement were a group, points 7 and 8 were disconnected and points from 8 onward were a group, GroupCount would be 2. |
| SegmentLen1 | The total of the distance from point 1 to point 2 for all measurements assigned to the item. |
| SegmentLen2 | The total of the distance from point 2 to point 3 for all measurements assigned to the item. |
| UncountedPts | The total of the uncounted points for all measurements assigned to the item. |
| Extension variable names | The total of the extension variable value for all measurements assigned to the item. Warning: Totaling extension variables is not always appropriate. For example, a variable such as WallHeight might be multipled by Length to calculate the area of a wall for an individual measurement. But the total of WallHeight across multiple measurements would not make sense. |
| Project | An item reference that starts at the Project node rather than from the current node. "Project" may only be used at the beginning of an operand. |
| Up | The quantity of the parent branch. to the item. "Up" may only be used at the beginning of an operand. |
| The Breakdown Code of any child branch | The quantity of the child branch. |
Breakdown codes sometimes take a form that is ambiguous. For example, a breakdown code of "10-3" might be interpreted as ten minus three (seven). To avoid this ambiguity, enclose the breakdown code in curly braces. In the example above, use "{10-3}".
The Area, Count, Length, Perim or Perimeter operands can be used as a suffix to the Up operand or the child branch operand. A dot character is used to separate the operands. To illustrate, consider the worksheet shown in Figure 1:
Figure 1: Sample Quantity Worksheet Window
The table below shows some possible operands for the item "45":
| Operand | Description |
|---|---|
| Up.Area | The total of the areas of all measurements assigned to the item "3". |
| Up.Up.Area | The total of the areas of all measurements assigned to the item "03" (CONCRETE, the parent of the parent of branch "15"). You can use "Up" multiple times (with separating dots) to reference a parent, a grandparent, etc. |
| Project.{02}.Area | The total of the areas of all measurements assigned to the item "02". You can use "Project" once at the start of an operand, followed by the breakdown code of its children, grandchildren, etc. |
| Foundation.Area | The total of the areas of all measurements assigned to the item "Foundation". |
| Foundation.Depth.Length | The total of the lengths of all measurements assigned to the item "Depth". You can use multiple breakdown codes (with separating dots) to reference children, grandchildren, etc. |
| Foundation.Width.Perim | The total of the perimeters of all measurements assigned to the item "Width". |
Dimension formula operands also include numeric literals (1, 2.3, 7, etc.) and string literals. String literals can be enclosed in single quotes ('ABC') or double quotes ("XYZ").
Operands are combined using operators and/or functions. The table below shows the operands and functions supported:
| Operation | Priority | Description |
|---|---|---|
| Math Operators | ||
| + | 4 | Add |
| - | 4 | Subtract |
| - | 6 | Unary minus |
| * | 5 | Multiply |
| / | 5 | Divide |
| Logical Operators | ||
| = or EQ | 5 | Equal to |
| >= or GE | 2 | Greater than or equal to |
| > or GT | 2 | Greater than |
| <= or LE | 2 | Less than or equal to |
| < or LT | 2 | Less than |
| <> or EQ | 2 | Not equal to |
| Text Operators | ||
| & | 4 | Concatenate text |
| \ | 4 | Concatenate text with a backslash(\) in between |
| Functions | ||
| Abs(N1) | 7 | Absolute value of N1 |
| And(C1,C2) | 7 | If C1 and C2 are both true, returns true, Otherwise returns false |
| ArcCos(N1) | 7 | Angle (in degrees) whose cosine is N1. |
| ArcSin(N1) | 7 | Angle (in degrees) whose sine is N1. |
| ArcTan(N1) | 7 | Angle (in degrees) whose tangent is N1. |
| Ceiling(N1,N2) | 7 | Returns the next value equal to or greater than N1 which is a multiple of N2. If N2 is omitted, returns the next whole number equal to or greater than N1. |
| Char(N1) | 7 | Character whose ASCII value is N1 |
| Clean(S1,S2) | 7 | Modify string S1 based on
the characters found in string S2. S2 may be one of more of the following: B - Remove beginning spaces E - Remove ending spaces L - Convert to lower case S - Remove all spaces U - Convert to upper case |
| Cos(N1) | 7 | Cosine of N1 degrees |
| DateTotDays(N1) | 7 | returns the date which is N1 days after December 31, 1969 |
| Day(D1) | 7 | Returns the day of the month for date D1. D1 should be of the form YYYYMMDD. |
| Floor(N1,N2) | 7 | Returns the next value less than or equal to N1 which is a multiple of N2. If N2 is omitted, returns the next whole number less than or equal to N1. |
| Frac(N1) | 7 | Fractional part of N1 |
| If(C1,X2,X3) | 7 | If C1 is true, returns X2. Otherwise returns X3. |
| Int(N1) | 7 | Whole number part of N1 |
| InStr(S1,S2) | 7 | Search string S2 for sub-string S1. Returns the starting position or zero if not found. |
| Len(S1) | 7 | Length of S1 (in characters). |
| Mid(S1,N2,N3) | 7 | Returns part of string S1, starting with character position N2, for N3 characters. If N3 is omitted, returns all characters starting with position N2. |
| Month(D1) | 7 | Returns the month of the year for date D1. D1 should be of the form YYYYMMDD. |
| Not(C1) | 7 | Returns true it C1 is false, false if C1 is true |
| Or(C1,C2) | 7 | If either C1 or C2 is true, returns true, Otherwise returns false |
| Round(N1,N2) | 7 | Round N1 to N2 decimal places. |
| Sin(N1) | 7 | Sine of N1 degrees. |
| Sqrt(N1) | 7 | Square root of N1. |
| Ssplit(S1,N1) | 7 | Returns the N1 value in the S1 string of semicolon separated values. So Ssplit("first;second;third", 2) would return "second". |
| Tan(N1) | 7 | Tangent of N1 degrees. |
| Today() | 7 | Returns todays date (yyyymmdd). |
| TotDays(D1) | 7 | Returns number of days between December 31, 1969 and date D1. D1 should be of the form YYYYMMDD. |
| Year(D1) | 7 | Returns the four digit year for date D1. D1 should be of the form YYYYMMDD. |
| YYMMDD(N1,N2,N3) | 7 | Returns the date for year N1 (2 digit or 4 digit), month N2 and day N3. |
Each operand has a Priority (shown in the second column above). Higher priority operations are performed first. Equal priority operations are performed left-to-right. Parentheses can be used to override priorities. The table below shows an example where multiplication is a higher priority that addition:
| Formula | Result |
|---|---|
| 1 + 2 * 3 + 4 | 11 |
| (1 + 2) * (3 + 4) | 21 |