Formulas establish and calculate mathematical relationships between elements of the spreadsheet. Whereas numeric entries remain the same until you change them, cells defined by formulas are automatically changed to reflect changes in referenced cells— even where there are complex interdependencies among cells.
Objective Grid formulas can calculate with numbers, text, logical values, cell references, and other formulas. For example, you can easily 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 entered in a cell, formulas are hidden behind the scenes, perform their work in the background, and display only the result of their calculation. To view the formula in a cell, simply select the cell.
Objective Grid provides an option that lets you make all formula expression visible (via CGXGridParam::m_nDisplayExpression).
Objective Grid also provides a wide array of functions that perform certain tasks. Functions can be used alone or in conjunction with formulas and other functions. Objective Grid provides many specialized functions in addition to those that are found in typical financial spreadsheets. See Section 14.3.5, "Built-in Functions."
The general form of an Objective Grid formula is:
= expression ; constraint expression // comment
In the above formula, expression defines the calculations needed to generate the cell's value, constraint expression places limits on acceptable values or the circumstances under which the calculation should take place, and comment is any text you want to attach to the cell.
The expression part of Objective Grid formulas looks just like an algebraic formula; it contains values and operators that define the relationships between values.
Objective Grid uses the following conventions for formulas:
A formula must begin with an equal (=) sign. When you begin typing into a cell, Objective Grid automatically assumes that you are 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 wish, but Objective Grid automatically removes them.
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.
Addresses of cell ranges such as B12..G29, A1..D5.
Absolute cell references denoted with dollar signs before the fixed coordinate ($A$1, $A1, or A$1), which will not be updated when the referencing cell is moved or copied.
Functions such as @SUM or @RADIANS, with their arguments.
Text surrounded by double quotation marks, such as "The sum is " or "Total".
User-defined cell names or cell range names, such as TOTALS or PROJECT1.
Operators are the characters that establish the relationships between values in a formula, such as +, -, * and <= .
Objective Grid supports all the arithmetic, Boolean, and logical operators available in the C programming language. Arithmetic operators calculate numeric values; text operators act on strings of text, and logical operators evaluate true/false conditions.
Objective Grid also provides two operators—exponentiation (**) and percent (%)— that are not available in the C language. It does not support the C address operators or the operators that have side effects, such as ++.
Objective Grid formulas can contain the following operators to define relationship between values.
| Operator | Definition |
| % 14 | Unary percent |
| ** 13 | Exponentiation |
| + 12 | Unary plus |
| - 12 | Unary minus |
| ~ 12 | Bitwise 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 |
| >= 8 | Greater Than or Equal |
| == 7 | Equal |
| != 7 | Not Equal |
| & 6 | Bitwise And, or String Concatenation |
| ˆ 5 | Bitwise Exclusive-Or (integer) |
| | 4 | Bitwise Or |
| && 3 | Logical And |
| || 2 | Logical Or |
| ?: 1 | Conditional |
In formulas with more than one operator, Objective Grid evaluates operators in the order of precedence presented above, with 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 Objective Grid operators:
The operators marked '(integer)' in Table 19 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.
Concatenation is linking two strings together. For example, if you concatenate (A1&A2) the string "John" in cell A1 with the string " Smith" in cell A2, the result is the value "John Smith."
The % operator also performs double duty: as the percent operator when appended to a number or numeric 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, Objective Grid considers string operands to be lower than numeric operands.
The conditional operator returns its second operand if its first operand evaluates True (non-zero) and returns its third operand if it evaluates False, (zero).
In formulas with conditional operators, the second and third operands may be any type Objective Grid supports, including ranges. For example, the expression =@SUM(A1 ? B1..C20 : C10..D15) returns the sum of B1..C20 if A1 evaluates to non-zero; otherwise it returns the sum of C10..D15.
Objective Grid accepts most arithmetic operators used in other spreadsheets like MS Excel, but there are a few differences in syntax and precedence.
The real power of Objective Grid lies in its ability to calculate relationships among different cells in the spreadsheet by typing the row/column coordinates, or address, in the formula.
Type the row and column coordinates of the cell in the formula. For example, to reference Row 5 in Column D, type D5.
Type the row and column coordinates of two cells in opposite corners of the block to be referenced, with two periods ( .. ) between the coordinates. For example, to reference the first five columns and the first five rows of the spreadsheet, type A1..E5.
This differs from Microsoft Excel. The equivalent Excel syntax would be A1:A5.
Objective Grid differentiates between relative, absolute, and indirect (or current cell) references. The latter is unique to Objective Grid.
Computed cell references are the result of a function that is itself a cell reference or range reference. See Section 14.6.11.3, "Computed Cell References," for more information about these.
Relative references are cell or range references that are interpreted relative to the current position of the cell containing the formula. Relative references are updated whenever a cell is copied or moved, to reflect the new position of the cell. By default, Objective Grid considers references to be relative.
Objective Grid tracks the referenced cell by considering its position relative to the formula cell, not by its address. For example, if the formula in cell A1 references cell B2, Objective Grid remembers that the referenced cell is one row down and one column right. If you copy the formula in cell A1 to another location (e.g., D17), the formula will reference the cell one row down and one column right of the new location (e.g., E18).
Absolute references are references to cells or ranges that remain fixed, no matter where the cell containing the formula is moved or copied. They are preceded with the dollar sign ($) before each coordinate to be fixed, such as $A1, A$1, or $A$1.
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 copy the formula in cell A1 to another location (e.g. D17), the formula still references cell B2. To specify an absolute cell address, insert a dollar sign ($) before the address coordinate to be fixed, or before both coordinates if both the row and column coordinates are to be fixed. For example: $B$2.
To specify all or part of a cell address to be absolute, insert a dollar sign ($) before the address coordinate to remain fixed. For example:
$B$5 makes the complete address absolute.
$B5 makes the column coordinate (B) absolute, the row coordinate (5) relative.
B$5 makes the column coordinate (B) relative, the row coordinate (5) absolute.
Refer to Section 14.2.6, "Range Specification," for information about the TextToRange() and RangeToText() methods.
To assign a name to a cell or range of cells, use the SetRangeName() command. To reference a cell or range by name, type the pre-assigned name of the cell or cell block into the formula.
Cell ranges are also relative, so when you move a cell range, references in formulas within that range are updated to reflect their new location. To specify an absolute range reference, insert dollar signs ($) before the coordinates in the formula. For example, to make the range A1..D5 absolute, type the reference as $A$1..$D$5.
To specify part of a cell range to be absolute, insert dollar signs only before the coordinates to remain absolute. For example, $A1..$D5 will fix the column coordinates of cell references but adjust the row coordinates to reflect the new location.
Refer to Section 14.3.6, "Ranges," for more information.
An indirect reference is a unique cell referencing technique in Objective Grid that allows you to refer to cells by row and/or column offset values relative to the current cell. This can be used anywhere a cell reference is expected:
function arguments
formulas
constraint expressions
Certain expressions within the context of Objective Grid require a means to express the current cell. Examples include the conditional statistical functions described in Section 14.7.3, "Conditional Statistical Functions," and constraint expressions described in Section 14.5.6, "Constraint Expressions."
References to cells in the neighborhood of the current cell are made with offset values enclosed in braces ( {} ) following the pound sign # which identifies the current cell. The offsets tell Objective Grid where to look, in relation to the current cell, for the cell being referenced. A negative row offset indicates a row above the current row. A negative column offset indicates a column to the left of the current column. Likewise, positive offset numbers for the row and column indicate a row below and a column to the right of the current cell.
The format is as follows:
#{column offset, row offset}
If you include only one value in the offset, Objective Grid assumes that it is a column offset. For example, the offset reference #{-1} tells Objective Grid to look to the column just left of the current cell.
The 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 to the 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.
@XVALUE("master.xs3", #) returns the value of the same cell reference in which this function is stored from the sheet indicated.
/verb/#-1+2/ adds 2 to the cell value from the cell to the left.
Constraints are limitations or conditions placed on the variables in your spreadsheet. They are expressed as algebraic statements appended to formulas. You can attach a constraint expression to any formula, by typing a semicolon (;) and the constraint conditions after the formula.
Constraint expressions establish conditions under which a formula operates or boundaries for valid results of the formula. Constraint expressions may be simple equality/inequality relationships, or they can be arbitrary formulas. Any valid Objective Grid expression that returns a numeric value is also a valid constraint expression. However, unlike the expression that defines a cell value, a constraint expression can reference the cell in which it resides, using the symbol #.
For example, the formula =A1 + A2 ; #>2 && #<=B5 || #==C7 means "the value of the current cell is the sum of cells A1 and A2, and that value must be either greater than 2 and less than or equal to the value of cell B5, or equal to the value of cell C7".
Constraint expressions are used, for example, in the conditional statistical functions. The benefit of constraint expressions is maximized when combined with current cell reference support (#) as indicated in the above example.
There may be instances where you need to force a recalculation when certain cell values change, when there is no implicit dependency in the formula that would trigger an automatic recalculation. This explicit dependency option is indicated by appending a backslash (\) to the end of the dependent formula. For example, the formula =@SUM(A1..A20)\D50 instructs Objective Grid to recalculate @SUM(A1..A20) whenever the contents of D50 change.
This feature is particularly important when you have a constraint expression containing an offset reference that produces a cell reference outside the cell range referenced in a dependent formula. Under these circumstances, Automatic Recalculation would not necessarily be triggered. In the above example, @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.
In order for C4 to be evaluated, it must be compared to C3 - which is not part of the explicit range, C4..C100. Without indicating an explicit dependency, C4 would never be evaluated properly. So, in this case, we would indicate the dependency as follows:
@CCOUNT(C4..C100, # #{0,-1})\C3..C99
which tells Objective Grid to recalculate whenever any cell in the range C3..C99 changes.
For more information about explicit dependency and computed cell references, see Section 14.6.11.3, "Computed Cell References."
Copyright © Rogue Wave Software, Inc. All Rights Reserved.
The Rogue Wave name and logo, and Stingray, are registered trademarks of Rogue Wave Software. All other trademarks are the property of their respective owners.
Provide feedback to Rogue Wave about its documentation.