3.2 SpreadScript Formulas

Formulas are the backbone of the spreadsheet, establishing and calculating 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.

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

SpreadScript provides a comprehensive set of built-in functions which are described in Chapter 4, SpreadScript Built-in Functions. SpreadScript functions which are compatible with Excel are listed in Table 4.2 on page .

3.2.1 Excel Compatibility Mode

There are some syntactic differences between native SpreadScript formulas and Excel formulas. While these differences are automatically translated when reading or writing Excel files, it may be more convenient for a user who is familiar with Excel to enter formulas using Excel's native syntax. The most commonly encountered example of this is the syntax for specifying ranges of cells. In SpreadScript, A1..C5 is the notation used to specify the rectangular array of cells whose upper left corner is A1 and lower right corner is C5. In Excel, same rectangular array is expressed as A1:C5.

To allow formulas to be entered using Excel's native syntax, SpreadScript includes an Excel compatibility mode. For applications intended to be used primarily with Excel spreadsheets, or for users who are most familiar with Excel formula syntax, it is recommended that SpreadScript be run in Excel compatibility mode. This is accomplished through the Perl API by calling spreadscript::setDefaultExcelMode(1) after calling spreadscript::init(), and similarly for the Python and Tcl APIs.

In addition to the range syntax, there are two other important points to note about Excel compatibility mode:

• There are several differences among formula operators between native mode SpreadScript and Excel. For example, the SpreadScript exponent operator is double-asterisk (**) while in Excel the operator is caret (^). When operating in Excel compatibility mode, SpreadScript expects the Excel operators in formulas. See Table 3.1 for a complete list of SpreadScript and Excel operators.
• There are a few incompatibilities between SpreadScript and Excel functions which have the same name. An example is the IPMT() (interest payment) function. The SpreadScript version of IPMT() returns a positive value, while the Excel version returns a negative value. When operating in Excel compatibility mode, SpreadScript will use the Excel version of IPMT(). See Table 4.2 for a complete listing of these functions.

Excel-compatible functions can be explicitly specified when SpreadScript is not operating in Excel compatibility mode by prefixing the function name with XL. For example, XLIPMT() specifies the Excel-compatible version of IPMT(). See Table 4.2 for a complete listing of functions which are compatible between SpreadScript and Excel.

3.2.2 Formula Syntax

The general form of an SpreadScript formula is:

= expression ; constraint expression // comment

where 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. Note that the ``constraint expression'' and ``comment'' portions of a formula are not supported by Excel, and will be lost when saving to an Excel .xls file.

The expression part of SpreadScript formulas looks just like an algebraic formula; it contains values and operators that define the relationships between values.

SpreadScript uses the following conventions for formulas:

• A formula must begin with an equal (=) sign. When a formula is entered into a cell using the Worksheet::setFormula() method, SpreadScript will automatically insert an equal sign if needed.
• Formulas can have as many as 4095 characters. (Note: Take care when creating very complex formulas to be imported into Excel. Excel is more limited than SpreadScript in the length and depth of nesting of formulas.) SpreadScript will automatically remove superfluous white space entered in a formula.

3.2.3 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.
• Addresses of cell ranges such as B12..G29, A1..D5, or A1:D5 (Excel compatibility mode).
• 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.
• User-defined cell names or cell range names, such as TOTALS or PROJECT1.
• SpreadScript functions, such as SUM or RADIANS, with their arguments.
• Text surrounded by double quotation marks, such as "The sum is " or "Total".

Note that Excel uses a colon (:) as the range operator; e.g., B12:G29. When entering a range in a SpreadScript formula, you may use the ``:'' notation only when operating in Excel compatibility mode. Otherwise, you must use the native SpreadScript ``..'' notation for ranges.

3.2.4 Formula Operators

SpreadScript 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 ++. SpreadScript provides two operators, exponentiation (**) and percent (%), that are not available in the C language.

The operators supported by SpreadScript are listed along with their Excel equivalents in Table 3.1. Note that there is no support in Excel for the bit-wise integer operators. Also note that some SpreadScript operators, such as logical not (!x) and remainder (x % y) are not available as operators in Excel, but can be emulated with function calls. SpreadScript will correctly performs these translations when saving a workbook as an Excel .xls file. When operating in Excel compatibility mode, use the operators listed in the ``Excel equiv.'' column, rather than the native SpreadScript operators. See Section 3.2.1 for more information on Excel compatibility mode.

Table 3.1: SpreadScript Operators

Operator	Precedence	Excel equiv.	Definition
x %	14	%	Unary percent
x ** y	13	^	Exponentiation
+ x	12	+	Unary plus
- x	12	-	Unary minus
~ x	12	N/A	Bitwise complement (integer)
! x	12	NOT(x)	Logical not
x * y	11	*	Multiplication
x / y	11	/	Division
x % y	11	MOD(x, y)	Remainder (integer)
x + y	10	+	Addition
x - y	10	-	Subtraction
x << y	9	N/A	Shift left (integer)
x >> y	9	N/A	Shift right (integer)
x < y	8	<	Less Than
x > y	8	>	Greater Than
x <= y	8	<=	Less Than or Equal
x >= y	8	>=	Greater Than or Equal
x == y	7	=	Equal
x != y	7	<>	Not Equal
x & y	6	N/A*	Bitwise And, or String Concatenation
x ^ y	5	N/A	Bitwise Exclusive-Or (integer)
x | y	4	N/A	Bitwise Or
x && y	3	AND(x, y)	Logical And
x || y	2	OR(x, y)	Logical Or
x ? y : z	1	IF(x, y, z)	Conditional

* - Ampersand (&) as a string concatenation operator translates correctly to Excel.

In formulas with more than one operator, SpreadScript evaluates operators in the order of precedence presented above, with highest precedence first. For example, AND/OR 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 SpreadScript operators:

• The operators marked ``(integer)'' in Table 3.1 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 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, SpreadScript considers string operands to be less 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 SpreadScript 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.

3.2.5 Cell Referencing in SpreadScript

SpreadScript differentiates between relative, absolute, and indirect references. The latter is unique to SpreadScript and not supported in Excel.

3.2.5.1 Relative Reference

SpreadScript 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, SpreadScript 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).

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

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.

3.2.5.3 Current Cell Reference

The notion of current cell reference is unique to SpreadScript and not supported in Excel. Do not use this feature when developing SpreadScript applications designed to be compatible with Excel.

Certain expressions within the context of SpreadScript require a means to express the current cell. The most common example is the conditional statistical functions described in Chapter 4, and constraint expressions described in section 3.2.6.

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 SpreadScript 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, SpreadScript assumes that it is a column offset. For example, the offset reference #{-1} tells SpreadScript 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 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.

#{-1}2+ adds 2 to the cell value from the cell to the left.

3.2.6 Constraint Expressions

Constraint expressions are unique to SpreadScript and not supported in Excel. Do not use this feature when developing SpreadScript applications designed to be compatible with Excel.

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 SpreadScript expression which 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 in several other contexts within SpreadScript, for example, the conditional statistical functions.

The benefit of constraint expressions is maximized when combine with current cell reference support (#) as indicated in the above example.

3.2.7 Explicit Dependency

Explicit dependencies in formulas are unique to SpreadScript and not supported in Excel. Do not use this feature when developing SpreadScript applications designed to be compatible with Excel.

There may be instances where you need to force a recalculation when certain cell values changes, when there is no implicit dependency in the formula that would trigger an automatic recalculation. This option is indicated by appending a backslash (\) to the end of the dependent formula. For example, the formula:
      =SUM(A1..A20)\D50  
instructs SpreadScript 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. Take for instance, the example from above:
      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 SpreadScript to recalculate whenever any cell in the range C3..C99 changes.