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 .
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:
**
) 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.
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:
Formulas can contain any or all of the following types of values:
"The sum is "
or "Total"
.
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:
&
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.
%
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.
==
and <
) can be used to
compare text strings lexically (alphabetically).
=SUM(A1 ? B1..C20 : C10..D15)
SpreadScript differentiates between relative, absolute, and indirect references. The latter is unique to SpreadScript and not supported in Excel.
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).
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:
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.
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}
#{-1}
tells SpreadScript to look to the
column just left of the current cell.
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.
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.
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.