- 3.2.1 Excel Compatibility Mode
- 3.2.2 Formula Syntax
- 3.2.3 Formula Values
- 3.2.4 Formula Operators
- 3.2.5 Cell Referencing in SpreadScript

- 3.2.6 Constraint Expressions
- 3.2.7 Explicit Dependency

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.

3.2.2 Formula Syntax

The general form of an SpreadScript formula is:

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.

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"`

.

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.

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.

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

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

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

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.

Grey Trout Software

02 March 2003