|
Formula Reference Guide |
|
Predictive Systems Lab Formulas
Formulas are an important part of an Predictive Systems Lab sheet. Formulas establish and calculate mathematical relationships among elements of the sheet. Cells defined by formulas are automatically changed to reflect changes in the referenced cells, even when there are complex interdependencies among cells. Numeric entries, on the other hand, remain the same until you change them.
Predictive Systems Lab formulas can perform calculations on numbers, text, logical values, cell references, and other formulas. For example, you can calculate the sum of a series of cells, the total of values in a column, a minimum or maximum value within a range, the rounded result of another formula, or the absolute value of a cell entry. Formulas can express complex interdependencies among cells, and they can define constraints on the calculation, such as limits on acceptable values or specific conditions under which a calculation should take place.
Once typed in a cell, formulas are hidden from view, perform their work in the background, and display only the result of their evaluation. To view the formula in a cell, simply select the cell for editing.
Formula Syntax
The general form of an Predictive Systems Lab formula is:
= expression // comment
Where expression defines the calculations needed to generate the value of the cell and comment is any text you want to attach to the cell. The expression part of Predictive Systems Lab formulas looks just like an algebraic formula; it contains values and operators, which define the relationships between values.
A constraint expression places limits on acceptable values or the circumstances under which the calculation should take place.
Predictive Systems Lab uses the following conventions for formulas:
| · | A formula must begin with an equal (=) sign. When you begin typing into a cell, Predictive Systems Lab automatically assumes youare typing a formula if you start with one of the following characters: |
0 1 2 3 4 5 6 7 8 9 . - =+
| · | Formulas can have as many as 511 characters. You can type spaces if you want, but Predictive Systems Lab automatically removes them. |
| · | To change a formula in a cell, move the mouse pointer to the cell and double-click the cell; alternatively, you can use the Edit Cell command on the Edit menu. |
Formula Values
Formulas can contain any or all of the following types of values:
| · | Numbers, such as 123, -123, 12.3. |
| · | Addresses of single cells, such as A1, D5, Z100. |
Refer to Specifying Cell Ranges and Locations for additional information on this subject.
| · | Addresses of cell ranges such as B12..G29, A1:D5. |
Refer to Specifying Cell Ranges and Locations for additional information on this subject.
| · | Relative cell references. |
| · | Text surrounded by double quotation marks, such as "The sum is " or "Total". |
Formula Operators
Predictive Systems Lab supports all the arithmetic, Boolean, and logical operators available in the C programming language. It does not support the C address operators or the operators that have side effects, such as ++. Predictive Systems Lab provides two operators, exponentiation (^) and percent (%), which are not available in the C language.
 | The bit-wise exclusive OR operator was set to double asterisks (**) rather than the caret (^) used in the C language, for compatibility with other spreadsheet formulas. |
Operator Precedence
% |
14 |
Unary percent |
^ |
13 |
Exponentiation |
+ |
12 |
Unary plus |
- |
12 |
Unary minus |
~ |
12 |
Bit-wise complement (integer) |
! |
12 |
Logical not |
* |
11 |
Multiplication |
/ |
11 |
Division |
% |
11 |
Remainder (integer) |
+ |
10 |
Addition |
- |
10 |
Subtraction |
<< |
9 |
Shift left (integer) |
>> |
9 |
Shift right (integer) |
< |
8 |
Less Than |
> |
8 |
Greater Than |
<= |
8 |
Less Than or Equal To |
>= |
8 |
Greater Than or Equal To |
== |
7 |
Equal |
!= |
7 |
Not Equal |
& |
6 |
Bit-wise And, or String Concatenation |
** |
5 |
Bit-wise Exclusive-OR (integer) |
| |
4 |
Bit-wise OR |
&& |
3 |
Logical AND |
|| |
2 |
Logical OR |
?: |
1 |
Condition |
In formulas with more than one operator, Predictive Systems Lab evaluates operators in the order of precedence presented above, with the highest precedence first. That is, AND/OR/NOT operators are evaluated after inequality operators in a logical expression, and multiplication/division operations are performed before subtraction/addition operations in an arithmetic expression. Operators at the same precedence level are evaluated from left to right. The precedence of operators can be overridden by using parentheses to explicitly specify the order of evaluation.
Here are some special notes about Predictive Systems Lab operators:
| · | The operators marked "(integer)" on the table above automatically convert their operands to integers. |
| · | The & operator performs double duty: as a bit-wise "and" if the operands are numbers, or as a string concatenation operator joining two strings together if the operands are text. |
| · | The % operator also performs double duty: as the "percent" operator when appended to a number or numerical expression, or as the C-style "modulus" operator when applied between two integer expressions. |
| · | Operators that define equality/inequality relationships (such as == and < ) can be used to compare text strings lexically (alphabetically). |
| · | In comparing mixed strings lexically, Predictive Systems Lab considers string operands to be lower than numeric operands. |
| · | The conditional operator returns its second operand if its first operand evaluates to true (non-zero) and returns its third operand if it evaluates to false (zero). |
| · | In formulas with conditional operators, the second and third operands may be any of the types Predictive Systems Lab supports, including ranges. For example, the expression: |
= SUM(A1 ? B:C20 : C10:D15)
Will return the sum of B1:C20 if A1 evaluates to non-zero; otherwise, it returns the sum of C10:D15.
Referencing Other Cells in Formulas
The real power of the formula engine lies in its ability to calculate relationships among different cells in the document worksheet. To reference other cells, type the row and column coordinates of the cell or cell range in the formula.
Refer to Specifying Cell Ranges and Locations for additional information on this subject.
Predictive Systems Lab differentiates between relative, absolute, and indirect references.
Absolute Reference
Absolute references remain the same, no matter where you move or copy the original formula. For example, if the formula in cell A1 references cell B2, and you move the formula in cell A1 to another location (e.g., D17), the formula will still reference the B2 cell. To specify an absolute cell or range address, just type the address of the referenced cell or range.
Relative Cell Reference
Certain formula expressions require a means to reference a cell relative to the current cell. The current cell is identified in any expression with a pound sign (#). References to cells in the neighborhood of the current cell are made with offset values enclosed in braces ( {} ) following the #. The offsets tell Predictive Systems Lab where to look in relation to the current cell for the cell being referenced. The format is as follows:
#{column offset, row offset}
If you include only one value in the offset, Predictive Systems Lab assumes that it is a column offset. For example, the offset reference #{-1} tells Predictive Systems Lab to look to the column just left of the current cell. Offset values may be constants or expressions.
Examples:
#{0,-1}
Refers to the cell above the current cell.
#{-2}
Refers to the cell two columns left of the current cell.
#{1}
Refers to the cell to the right of the current cell.
#{0,1}
Refers to the cell below the current cell.
CSUM(C4..C100, #{-1} == "Joe")
Calculates the sum of all the values in the range C4..C100 for which the cell in the column to the left contains the string "Joe."
CCOUNT(C4..C100, # > #{0,-1})
Counts all the cells in the range C4..C100 whose value is greater than the contents of the cell immediately above.
#{-1}+2
Adds 2 to the cell value in the cell to the left.
Alternatively, you can use the CELLREF and the RANGEREF formula functions to reference relative cells and ranges.
Constraint Expressions
Constraints are conditional expressions that can be placed in some formula functions, and that are expressed as algebraic statements. Constraint expressions establish conditions under which a formula function operates or boundaries for valid results of the function. They may be simple equality/inequality relationships, or they can be arbitrary formulas. Any valid formula expression that returns a numeric value is also a valid constraint expression. A constraint expression can reference the cell in which it resides using the symbol #. For example, the formula:
= CMAX(A1:A6, #<SUM(A11, A12)) = 56
Where A1:A6 = 4, 56, 33, 100, 0, -1, A11=50 and A12=50.
means "the value of the current cell is the maximum value in the range A1:A6 such that this value is less than the sum of cells A11 and A12".
Constraint expressions are used in several formula functions in Predictive Systems Lab, including conditional statistical functions. The benefit of constraint expressions is maximized when combined with the current cell reference symbol (#), as indicated in the above example.
You can use regular expressions to define text (non-numeric) searches. Regular expressions specify a set of strings of characters. In a regular expression, a character normally matches with the same character in the matching string. A few characters have special meanings:
| . | A period matches any character (for example, A.C matches "ABC" or "AXC"). |
| ^ | Matches only at the beginning of a field (for example, ^ABC matches "ABC" but not "AABC"). |
| $ | Matches only at the end of a field (for example, ABC$ matches "AABC" but not "ABCD"). |
| [x-y] | Matches any character between the character x and the character y (for example, [0-5]$ finds fields ending in the characters 0 through 5). |
| [xyz] | Matches any character in the list xyz (for example, [05]$ finds fields ending in either a 0 or a 5). This form and the one above can be combined. [27-9]$ finds fields ending with the characters 2, 7, 8, or 9. |
These methods can be combined. Thus, the filter [brc]at finds "bat", "rat", and "cat".
Sponsored
Try Predictive Systems Lab
Need Help?
Contact support