H1: Spreadsheet calculations
H2: Overview
Spreadsheet style calculations have been introduced into SDF using the
standard {{E<2[> E<2]>}} syntax with a prefix of {{+}} (or {{=}}) indicating
that the expression is to be evaluated by the calculation routines.
This extension has been loosely modelled on [[Microsoft]] {{Excel}}[[r]]
in terms of the initial functions supported and the syntax used.
The spreadsheet expression evaluator and the documentation below
was written by Tim Hudson ({{EMAIL:tjh@cryptsoft.com}}).
H2: Cells and Cellids
Each {{cell}} in a table has an {{cellid}} which is made up of a
single uppercase letter indicating the column index and a number
indicating the row index (counting from 1 and excluding the
heading rows). The upper left {{cell}} is hence {{A1}}.
An example grid indicating {{cellid}}s:
!block table; narrow
Title1:Title2:Title3:Title4:Title5
A1:B1:C1:D1:E1
A2:B2:C2:D2:E2
A3:B3:C3:D3:E3
...:...:...:...:...
A100:B100:C100:D100:E100
!endblock
A range of {{cellid}}s is specified using the syntax {{:cellid1:cellid2}}.
For example: {{:A1:C1}} is exactly the same as {{A1,B1,C1}}
H2: Spreadsheet Expressions
An expression consists of a combination of standard Perl operators
and spreadsheet functions and {{cellid}}s or {{cellid}} ranges.
Standard Perl operators include:
* + - * /
H2: Spreadsheet Functions
Spreadsheet functions use the syntax {{FUNCTION(ARG1,ARG2,...ARGN)}}.
The following functions are supported:
* {{AVERAGE}} - the average - SUM(ARGS)/COUNT(ARGS)
* {{SUM}} - the sum of the args - same as ARG1+ARG2+...+ARGN
* {{MIN}} - the minumum argument value
* {{MAX}} - the maximum argument value
* {{COUNT}} - the number of arguments
* {{PRODUCT}} - the product of the args - same as ARG1*ARGN*...*ARGN
* {{ROWSUM}} - the {{SUM}} of all the cells in the row to the left of
the current cell
* {{ROWPROD}} - the {{PRODUCT}} of all the cells in the row to the
left of the current cell
* {{COLSUM}} - the {{SUM}} of all the cells in the column above the
current cell
* {{COLPROD}} - the {{PRODUCT}} of all the cells in the column above the
current cell
H2: An Example
A simple example is shown below:
!block verbatim
!block table; style="grid"
Count Price Total
10 5 [[=A1*B1]]
15 5.23 [[=ROWPROD]]
[[=COLSUM]] [[=B1+B2]] [[=COLSUM]]
!endblock
!endblock
This generates the result below. (Ok, summing two prices is
meaningless, but it illustrates the syntax.)
!block table; style="grid"
Count Price Total
10 5 [[=A1*B1]]
15 5.23 [[=ROWPROD]]
[[=COLSUM]] [[=B1+B2]] [[=COLSUM]]
!endblock
H2: Accessing Spreadsheet Values in Paragraph Text
Values are available until the next table is processed so
you can refer to data inside {{normal}} paragraphs after
the table like this E<2[>=A1E<2]> (which evaluates to [[=A1]]).
H2: Recursive Expression Evaluation
A spreadsheet expression will recursively evaluate any expressions
contained in {{cells}} that are used in an expression. In the example
above, the expression in {{cell}} {{C3}} depends on the results of the
expression in {{cell}} {{C1}} and {{C2}}.
H2: Disabling Calculations
Calculation support for a table can be disabled by adding in
an attribute of {{nocalcs}}. (Without this, the pointers required to table
data that are needed when doing spreadsheet calculations occur for each
table cell.)