Charter Formulas

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.

Charter 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.

The charter provides an option that lets you make all formula expression visible (via CGXGridParam::m_nDisplayExpression).

The charter also provides a wide array of functions that perform certain tasks. Functions can be used alone or in conjunction with formulas and other functions. The charter provides many specialized functions in addition to those that are found in typical financial spreadsheets.

Formula Syntax

The general form of a charter 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 charter formulas looks just like an algebraic formula; it contains values and operators that define the relationships between values.

The charter uses the following conventions for formulas:

0 1 2 3 4 5 6 7 8 9 . - @ =+

Formula Values

Formulas can contain any or all of the following types of values:

Formula Operators

Operators are the characters that establish the relationships between values in a formula, such as +, -, * and <= .

The charter 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.

The charter 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 ++.

Charter formulas can contain the following operators to define relationship between values.

 

Operator Precedence Definition

 

Operator

Precedence

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, the charter 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 charter operators:

Referencing Other Cells in Formulas

The real power of the charter lies in its ability to calculate relationships among different cells in the spreadsheet by typing the row/column coordinates, or address, in the formula.

To reference a cell by address:

Type the row and column coordinates of the cell in the formula. For example, to reference Row 5 in Column D, type D5.

To reference a contiguous group of cells by address:

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.

Note: This differs from Microsoft Excel. The equivalent Excel syntax would b A1:A5.

Cell referencing in the charter

The charter differentiates between relative, absolute, and indirect (or current cell) references. The latter is unique to the charter.

Computed cell references are the result of a function that is itself a cell reference or range reference. For more information, see Computed Cell References.

Relative Reference

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, the charter considers references to be relative.

The charter 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, the charter 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 Reference

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:

Named Ranges

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.

Indirect (or Current Cell Offset) Reference

An indirect reference is a unique cell referencing technique in the charter 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:

Certain expressions within the context of the charter require a means to express the current cell. Examples include the conditional statistical functions described in Conditional Statistical Functions, and constraint expressions described in 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 the charter 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}

Examples:

Constraint Expressions

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 charter 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.

Explicit Dependency

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 the charter 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 the charter to recalculate whenever any cell in the range C3..C99 changes.

For more information about explicit dependency and computed cell references, see Computed Cell References.